 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Delete a row into a new table?
Hi, Is it possible (using T-SQL) to delete a row from one table and insert it into another? e.g. I have a table dbo.employee, an employee leaves, so I delete their record, but want to place it into dbo.employee_old  SignatureGerry Hickman (London UK) Sure but not in one statement. BEGIN TRAN INSERT INTO dbo.employee_old (column list) SELECT Column list from dbo_employee WHERE Employee_ID = xxx DELETE FROM dbo.employee WHERE Employee_ID = xxx COMMIT TRAN Of course you may want to put some error logic in there but that is the general idea. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hi, > > Is it possible (using T-SQL) to delete a row from one table and insert it > into another? e.g. I have a table dbo.employee, an employee leaves, so I > delete their record, but want to place it into dbo.employee_old > Is it possible (using T-SQL) to delete a row from one table and insert it > into another? e.g. I have a table dbo.employee, an employee leaves, so I > delete their record, but want to place it into dbo.employee_old If you are using SQL 2005, you can use the OUTPUT clause. For example: DELETE FROM dbo.employee OUTPUT deleted.* INTO dbo.employee_old WHERE employee_id = 1 SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Hi, > > Is it possible (using T-SQL) to delete a row from one table and insert it > into another? e.g. I have a table dbo.employee, an employee leaves, so I > delete their record, but want to place it into dbo.employee_old Good point Dan. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> Is it possible (using T-SQL) to delete a row from one table and insert it >> into another? e.g. I have a table dbo.employee, an employee leaves, so I [quoted text clipped - 12 lines] >> into another? e.g. I have a table dbo.employee, an employee leaves, so I >> delete their record, but want to place it into dbo.employee_old > Good point Dan. Interesting! Thanks for the ideas; I've just realized this database is on SQL 2000, but hope to have it moved to SQL 2005 soon, and the OUTPUT clause looks useful. SignatureGerry Hickman (London UK) Why not use triggers :) - Sha Anand > Hi, > > Is it possible (using T-SQL) to delete a row from one table and insert > it into another? e.g. I have a table dbo.employee, an employee leaves, > so I delete their record, but want to place it into dbo.employee_old > Why not use triggers :) Thanks, I'll have a look at triggers in the BOL. SignatureGerry Hickman (London UK) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.