November 16, 2009 at 5:01 pm
For a script we are running, there are times when we want to move records from one table to another (in other words, copy the records into a new table, then delete the records from the old table). The criteria to determine these records is actually pretty complicated, so I was wondering if there was a way to combine both the delete and insert operations into a single step based on my criteria.
To show it in code I would like to convert the following:
SELECT stuff
INTO NEW TABLE
FROM TABLE
WHERE STUFF = 'BLAH'
DELETE FROM TABLE
WHERE STUFF = 'BLAH'
into a single step:
(in pseudocode)
Insert into newtable and delete from Table
WHERE stuff = 'blah'
November 16, 2009 at 6:15 pm
Is there an estimate of how many records you are going to move per batch or per Day etc...
As you have mentioned you have certain criteria that needs to meet. Not sure of what that criteria is, I recollected if such a functionality was ever implemented by me, the answer is no, I have not done this before.
But would like to suggest Insert Triggers. It may sound weird, foolish or crazy but I have not tried this before, so not sure if there is any better way to do this, just wanted to give my input.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 6:24 pm
You could use the OUTPUT clause of the DELETE statement, but the target table would not be allowed to have any enabled triggers, check constraints, rules, or take part in a foreign key relationship.
The selection criteria sound too complex for a partitioning-based solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply