1

I am using SQL Server 2014 Management Studio. I am adding a delete feature on my application that will delete a row from Table1. It will first insert that row into Table2 and then delete it from Table1.

To get a duplication of Table1's columns for Table2, I could do this:

SELECT * INTO Table2 FROM Table1

However, is there a way to have Table2 automatically get all columns from Table1 on a constant basis? The reason I ask is because Table1 will have different columns added in the future, so having to manually duplicate this into Table2 will be an annoyance. Thanks!

RockOn
  • 197
  • 1
  • 19
  • 1
    You need trigger to do that, check this one http://stackoverflow.com/questions/9996643/sql-server-on-delete-trigger , of course you'll probably have to modify code a bit, but that is solution for your problem – Veljko89 Jan 13 '16 at 14:07

1 Answers1

4

There are couple of ways to do this:

  • You could use a trigger, that would catch DELETE operations
  • You could modify you DELETE statement to use OUTPUT

It would look like this:

DELETE T1
OUTPUT DELETED.* INTO Table2
FROM Table1 AS T1;

So whenever you delete something from Table1, these records will be added to Table2.

If Table2 is supposed to be an archive table, it could have a ArchiveDate column, so then your statement could be potentially improved and look like this:

DELETE T1
OUTPUT DELETED.*, CURRENT_TIMESTAMP INTO Table2
FROM Table1 AS T1;

Of course I would not recommend using * in your SQL Queries and list your columns carefully:

DELETE T1
OUTPUT DELETED.Column1, DELETED.Column2, DELETED.Column3, DELETED.Column4, CURRENT_TIMESTAMP
INTO Table2 (Column1, Column2, Column3, Column4, ArchiveDate)
FROM Table1 AS T1;

This way, you'll be able to know when that specific record was deleted.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107