Move row from one table to another?
11,270
for SQL Server 2005 and up, try the OUTPUT Clause (Transact-SQL) clause:
DELETE OldTable
OUTPUT DELETED.col1, DELETED.col2...
INTO NewTable
WHERE ID=...
Working example:
DECLARE @OldTable table(col1 int, col2 varchar(5), col3 char(5), col4 datetime)
DECLARE @NewTable table(col1 int, column2 varchar(5), col3 int , col_date char(23), extravalue int, othervalue varchar(5))
INSERT @OldTable VALUES (1 , 'AAA' ,'A' ,'1/1/2010' )
INSERT @OldTable VALUES (2 , 'BBB' ,'12' ,'2010-02-02 10:11:22')
INSERT @OldTable VALUES (3 , 'CCC' ,null ,null )
INSERT @OldTable VALUES (4 , 'B' ,'bb' ,'2010-03-02' )
DELETE @OldTable
OUTPUT DELETED.col1
,DELETED.col2
,CASE
WHEN ISNUMERIC(DELETED.col3)=1 THEN DELETED.col3
ELSE NULL END
,DELETED.col4
,CONVERT(varchar(5),DELETED.col1)+'!!'
INTO @NewTable (col1, column2, col3, col_date, othervalue)
OUTPUT 'Rows Deleted: ', DELETED.* --this line returns a result set shown in the OUTPUT below
WHERE col1 IN (2,4)
SELECT * FROM @NewTable
OUTPUT:
col1 col2 col3 col4
-------------- ----------- ----- ----- -----------------------
Rows Deleted: 2 BBB 12 2010-02-02 10:11:22.000
Rows Deleted: 4 B bb 2010-03-02 00:00:00.000
(2 row(s) affected)
col1 column2 col3 col_date extravalue othervalue
----------- ------- ----------- ----------------------- ----------- ----------
2 BBB 12 Feb 2 2010 10:11AM NULL 2!!
4 B NULL Mar 2 2010 12:00AM NULL 4!!
(2 row(s) affected)
Related videos on Youtube
Comments
-
lance about 2 years
I have two tables with the same column definitions. I need to move (not copy) a row from one table to another. Before I go off and use INSERT INTO/DELETE (in a transaction), is there a smarter way?
SQL Server 2005
-
Daniel Vassallo about 14 years@KM: +1. I was not aware of this. Would you recommend this method over
INSERT
andDELETE
? -
KM. about 14 yearsI would use this over an INSERT and a DELETE every time, SQL Server can probably optimize it better and it guarantees you INSERT and DELETE only the same rows, no more and no less.
-
lance about 14 yearsThis is one statement, so... no transaction necessary, I guess?
-
KM. about 14 yearsOP is using SQL Server 2005, so the OUTPUT Clause (Transact-SQL) can do it in a single command, see my answer...
-
KM. about 14 yearsOP is using SQL Server 2005, so the OUTPUT Clause (Transact-SQL) can do it in a single command, see my answer...
-
KM. about 14 years@lance, unless you have other database changes that you want to tie to this command, then no.
-
KM. about 14 years@lance, I've added a working example, if that doesn't answer your question, provide more specifics on the columns in the tables that are having questions about.
-
lance about 14 yearsI'm in great shape. It was an absolute long shot, my asking this question. I just thought I'd ask the smart people before doing "what I know". It's so satisfying getting such a great answer, and it's even more satisfying seeing the multiple other people who thought as I did and have learned this "new" technique from my question. Thanks so very much.
-
joshmax over 12 years@KM. This topic is a bit old, but if you need to move the row w/o one of the columns and add in a new column, would you still suggest using this?
-
KM. over 12 years@Max, If you need to capture deleted rows, there is no better way than using OUTPUT. If you need another column I'd UPDATE and join it on after having the populated the rows with the DELETE OUTPUT.