INSERT into another table, and then DELETE from the source table

sql, sql-server-2005

Solution

Use

WITH  q AS
        (
        SELECT TOP 18 *
        FROM    pippo
        ORDER BY FIELD1 ASC /* You may want to add ORDER BY here */
        )
DELETE
FROM    q
OUTPUT DELETED.* INTO TableNew

Problem

How can I delete the first 18 rows (sorted by FIELD1) from the "pippo" table, and then insert THE SAME 18 rows in another clone-table called minnie? The following code extracts the first 18 rows ordering by FIELD1, and then deletes them: ``` WITH q AS ( SELECT TOP 18 * FROM pippo ORDER BY FIELD1 ASC /* You may want to add ORDER BY here */ ) DELETE FROM q ``` How can I insert the same 18 rows into the minnie table, before deleting them? Thank you in advance for your kind help.

Original source