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'.

Answer 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)