Monday, 2 September 2013

Delete and Insert in single operation

have two tables with same structure i wanna delete one row from one table and insert that same row into other table

Usually we will make use of Triggers in this scenario but with out using the trigger we can get this done.

See the below query
 
Create Table #t1(id int, name varchar(50))
go
Insert into #t1 values (1,'aaaa')

DELETE #t1
OUTPUT DELETED.ID, DELETED.name
INTO #t2(id,name)
FROM #t1
WHERE id=1