CODEWITHSUNDEEP
sqlsql-server-2005
UltraCommit
UltraCommit

Reputation: 2276

First INSERT, then - if OK - DELETE

This SQL SERVER 2005 statement first deletes 18 record ordering them by FIELD1 ASC, then inserts the same 18 records in a clone table:

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

Please help me to convert the previous statement in order to first INSERT, and THEN - IF INSERT HAS BEEN OK - delete the same 18 records; I've just tried but the following code gives the error,

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'INSERTED'.

WITH  q AS
        (
        SELECT TOP 18 *
        FROM    pippo
        ORDER BY ID_OL ASC /* You may want to add ORDER BY here */
        )
insert into tablenew select * from q OUTPUT INSERTED.* INTO TABLENEW

Thank you in advance for your kind help.

Upvotes: 3

Views: 299

Answers (2)

roman
roman

Reputation: 117465

declare @some_temp_table table (ID_OL int)

WITH  q AS
        (
        SELECT TOP 18 *
        FROM    pippo
        ORDER BY ID_OL ASC /* You may want to add ORDER BY here */
        )
insert into minnie
OUTPUT INSERTED.ID_OL INTO @some_temp_table
select * from q

delete from pippo where ID_OL in (select ID_OL from @some_temp_table)

another version

set xact_abort on

declare @filter table (ID_OL int primary key)

insert into @filter (ID_OL)
SELECT TOP 18 ID_OL
FROM pippo
ORDER BY ID_OL ASC

begin transaction

insert into minnie 
select * from pippo where ID_OL in (select ID_OL from @filter)

delete from pippo where ID_OL in (select ID_OL from @filter)

commit transaction

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

insert into temptable
SELECT TOP 18 * FROM  pippo ORDER BY FIELD1 ASC

if(@@ROWCOUNT = 18)
BEGIN
WITH  q AS
        (
        SELECT TOP 18 *
        FROM    pippo
        ORDER BY ID_OL ASC /* You may want to add ORDER BY here */
        )

delete from q 
END

Upvotes: 1

Related Questions