Active6 years, 2 months ago
When a user deletes a row in the database, I want to archive it into a separate table in the database rather than delete it or flag it in the current table. I figure I would need to do something like in this link:
The thing is, the archive table has 1 extra column in it that does not match the original table (ArchiveTimeStamp). This ArchiveTimeStamp does not exist in the original table, instead I would use something like
This is what I have so far:
Another useful tool provided by ApexSQL that can be used to copy SQL Server tables data and schema from the source database to the destination one is the ApexSQL Script tool. This nice tool will create script for the database tables schema and data with the indexes and keys of these tables handling the Identity column insert.
Is there a way for me to modify the SqlCommand to add another param that doesn't exist in the original Table?
Community♦
KevinKevin1,24466 gold badges2929 silver badges4444 bronze badges
6 Answers
![Row Row](https://www.mssqltips.com/tipImages2/6009_options-copy-sql-server-table-between-databases.005.png)
Why not just handle this on the back end? You can create a trigger on the original table to insert into another table after every delete?Your trigger will look like this:
When a record is deleted on the original table, it will insert the deleted record into the other table. You might want to read on using the
deleted
table here.Check this SQL Fiddle. Since you're inserting the timestamp in another column, you can just add this on the
INSERT INTO SELECT
statement:This could be the query for your trigger:
Gian AcunaGian Acuna
Good question. I'd suggest (as Gian has also suggested) moving the logic you require to backup the deleted row into a trigger that gets fired on delete.
Triggers are events in a database associated to a table which get fired upon an action occurring i.e. insert / update / delete.
So in your scenario, if you create an
ON DELETE
trigger in the source table, it will get fired when a delete occurs. The SQL contained within the trigger can specify what to do with the deleted data, which in your scenario will be: insert the deleted info into the archive table with a timestamp.So if you have:
You'll need to create a
FOR DELETE
trigger against Source_Table (something like this):The above is some rough SQL which may contain a couple of syntax errors but the guts of the idea is conveyed.
Michael Petrotta53.5k1414 gold badges131131 silver badges173173 bronze badges
Taz HusseinTaz Hussein
You will have to use to explicit column list and values form of the INSERT statement:
See Insert into ... values ( SELECT ... FROM ... )
Community♦
Keith PayneKeith Payne
I think you have to specify the columns with something like this
COLD TOLDCOLD TOLD11.9k33 gold badges2828 silver badges4747 bronze badges
You need to specify the columns name in that case:
NeverHopelessNeverHopeless9,89344 gold badges2929 silver badges5252 bronze badges
Here is my suggestion, you are forced to supply the column names or it won't let you run the query, however I understand you would prefer a generic solution that worked for any table so I suggest building the insert SQL dynamically, cache it on your application, and then just execute it with your extra archive column. Here is a c# example:
You would use it with something like:
user2535425