insert into select + delete?

[hessodreamy]

Is there a way to copy some data from 1 table to another in a insert into...select statement AND remove the affected data from the original table?

ie combining these 2 statements:

insert into superCat_subCat (sCatId, subCatId) select from cat_subCat where catId = 327

delete from cat_subCat where catId = 327

 

[SQLDenis]

As far as I know, this is not possible

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[KarveR]

I was gonna say that to see if TonyGroves could contradict me



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

No, you weren't!

 

[KarveR]

Result!

Now, for the original answer:
No.

Personally I'd dump the data to the new table and flag it as dumped, then say a week later when I've had a chance to validate, I'd delete it from the master table.

Depends how busy your database is as to what extremes you go to, with ours we can "lose" around 600 entries in the time it takes to type "select".

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.