Archiving the output from Delete Statement in Sql Server

Question

I'm trying to archive the data into table (EMPTAB_Archive) by deleting the data from Production table (EMPTAB) using the temporary table (##TMP) as a reference.

Note that temporary table contains two fields: empcode and empname, this is a part of code from a stored procedure that isn't working:

SELECT @SQL =          'DELETE ET1
                        FROM EMPDB..EMPTAB ET1 DELETED.*
                        INTO [EMPDB_ARCHIVE]..EMPTAB_Archive
                        INNER JOIN ##TMP TMP1
                        WHERE EMPID IN ##TMP
                        AND TMP1.DOCUMENTID = ET1 .EMPID'
         EXEC(@SQL)

But i'm getting below error message

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'DELETED'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '##TMP'.

Was this code copied from a trigger? I think DELETED is a special object that's only available when the trigger fires (like INSERTED and UPDATED). — booyaa, Nov 06 '13 at 12:11
Look at the `OUTPUT` clause. What is `WHERE EMPID IN ##TMP` supposed to do? Where is the `ON` clause for the `JOIN`? — Martin Smith, Nov 06 '13 at 12:15
@MartinSmith WHERE EMPID IN ##TMP gets only those employee ids that i have retrieved and stored in temporary table — Sreedhar Danturthi, Nov 06 '13 at 12:27

Bogdan Bogdanov · Accepted Answer · 2013-11-06T12:51:40.217

1

The problem is in WHERE EMPID IN ##TMP. You have to use join. Also deleting into is also not correct.

WHERE EMPID IN ##TMP must be done by join.

I think that correct SQL should looks like:

SELECT @SQL = 'DELETE ET1 OUTPUT DELETED.* 
INTO [EMPDB_ARCHIVE]..EMPTAB_Archive
FROM EMPDB..EMPTAB ET1 
INNER JOIN ##TMP TMP1 ON 
TMP1.DOCUMENTID = ET1 .EMPID'
EXEC(@SQL)

edited Nov 06 '13 at 12:51

answered Nov 06 '13 at 12:35

Bogdan Bogdanov

1,707
2
20
31

score 0 · Answer 2 · answered Nov 06 '13 at 12:56

0

Also you do not need Dynamic SQL which is in this case subject to SQL Injection. Just use Static SQL

answered Nov 06 '13 at 12:56

Madhivanan

13,470
1
24
29

Correct, you can execute this without dynamic SQL. – Bogdan Bogdanov Nov 06 '13 at 13:02

Archiving the output from Delete Statement in Sql Server

2 Answers2