CODEWITHSUNDEEP
sqldatabasedelete-row
sahibeast
sahibeast

Reputation: 351

How to get list of deleted records in SQL

My goal is to separate two types of data in table that is being sent to a stored procedure. In this table, I will have two kinds of records of type 1 and type 2, let's say.

I want to delete all data of type 2 from the inputted table but still have it stored in a separate temp table.

I know how to delete data with the following statement:

DELETE t
FROM @tags t    
WHERE t.Type = 2

Is there a way to retrieve the deleted records so I can insert them into a separate temp table?

Otherwise I will have to have a separate code block before that looks like the following:

INSERT @dynamicTags(String)
SELECT String
FROM @tags t
WHERE t.Type = 2

Any ideas to combine the two above statements into one?

Upvotes: 0

Views: 2353

Answers (2)

Andreas
Andreas

Reputation: 5103

If you are using postgres you can use the returning clause: http://www.postgresql.org/docs/9.3/static/sql-delete.html

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

If using SQL Server you can do this with the OUTPUT clause:

DELETE t
FROM @tags t    
OUTPUT DELETED.* INTO @MyTableVar
WHERE t.Type = 2

Upvotes: 4

Related Questions