1

I wonder how you can write a T-SQL DELETE statement using OUTPUT to spit out INSERT statements for those rows that you have deleted (as a rollback plan in the future) instead of just simply outputting the rows that are deleted.

A generic example or code snippet would be up voted.

Include also statement as a whole to export those INSERT statements into a file (e.g. using BCP) will be accepted as the answer.

Server is SQL Server 2008R2

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1589188
  • 5,316
  • 17
  • 67
  • 130

1 Answers1

1

If I understand your question what you want is to create a file with the insert statements that would undo a delete. If so one way to do this that should work is this:

-- set up some test data
DECLARE @table1 TABLE (id INT, name VARCHAR(32))
INSERT INTO @table1 VALUES (1, 'Fred'),(2, 'Tom'),(3, 'Sally'),(4, 'Alice')

-- drop and create a temporary table to hold the statements to output
IF OBJECT_ID('TempOutputTable', 'U') IS NOT NULL
DROP TABLE TempOutputTable 
CREATE TABLE TempOutputTable (SqlStatement VARCHAR(MAX))

-- delete a couple of rows from @table1
DELETE FROM @table1
OUTPUT 'INSERT table1 (id, value) VALUES (' + CAST(deleted.id AS VARCHAR(10)) + ', ''' + deleted.name + ''')' AS SqlStatement 
INTO TempOutputTable
WHERE id = 4 OR id = 2

-- export using bcp
DECLARE @SqlCommand sysname
SELECT @SqlCommand = 'bcp "select * from test.dbo.TempOutputTable" queryout D:\mytest.txt -c -T'
EXEC xp_cmdshell @SqlCommand, NO_OUTPUT

-- remove the temporary table
DROP TABLE TempOutputTable

After running this the file D:\mytest.txtwill contain this:

INSERT table1 (id, value) VALUES (2, 'Tom')
INSERT table1 (id, value) VALUES (4, 'Alice')

This assumes that xp_cmdshell is enabled (to be able to run bcp) and of course you would have to adjust the insert statement according to your needs.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • Thanks! Any chance you can make it without a temp table (i.e. OUPUT INTO @tableVar) and make the INSERT statement generic without knowing the table structure (i.e. in your example, (id, value) and the rest can be auto gen)? – user1589188 Mar 27 '14 at 07:05
  • @user1589188 I think it would be possible to make the statement generic, but I also believe that the temp table is necessary as you need a way to persist the data between the delete session and the bcp session which runs from the outside. I can look into it further later today. – jpw Mar 27 '14 at 07:15
  • Yes please, a generic one allows me to use it across many tables without tweaking. Nevertheless, your answer is very clever already. – user1589188 Mar 27 '14 at 07:27
  • 1
    @user1589188 I've spent some time looking into this now, and while it certainly can be done in a generic fashion I believe it's a fairly complex process that would take several hours to get working, and I don't really have the time to do it. I think your best option might be to either look at this post: http://stackoverflow.com/questions/7515110/create-sql-insert-script-with-values-gathered-from-table or maybe look into how to use powershell to use the 'generate scripts' function. – jpw Mar 27 '14 at 13:58
  • @user1589188 All in all it seems like a lot of work. You might want to consider inserting the deleted records in a backup table instead. That approach would make it very easy to restore deleted information, and it would be a lot easier to create such a solution. – jpw Mar 27 '14 at 13:58