CODEWITHSUNDEEP
sql-serversql-server-2008
Kundan
Kundan

Reputation: 109

Delete Record from one table and insert into another table

I have table test1 with fields Id and Date. ID with primary key which is being referenced in another table test2 having ID and name field. Here Id references Id from test1.

I want to delete last 3 months data from test1 and test2 and insert the deleted data into tables test3 (from test1) and test4 (from test2).

Upvotes: 0

Views: 7582

Answers (1)

trincot
trincot

Reputation: 350715

Assuming that the structure of table test3 is the same as test1 and that of test4 is the same as test2, execute these statements.

INSERT INTO test3
SELECT * FROM test1
WHERE Date < dateadd(month, -3, GETDATE());

INSERT INTO test4
SELECT * FROM test2
WHERE ID IN (SELECT ID FROM test3);

DELETE FROM test2
WHERE ID IN (SELECT ID FROM test3);

DELETE FROM test1
WHERE ID IN (SELECT ID FROM test3);

Don't forget to commit changes. If you create a script for this to be launched regularly, make sure to perform these statements in a transaction block with a COMMIT TRANSACTION at the end.

Upvotes: 1

Related Questions