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
A:
There is no such thing as a MOVE command in SQL. You'll have to first insert from table 1 to table 2 Then remove the copy from table 1.
JonH
2010-05-13 19:13:55
OP is using SQL Server 2005, so the [OUTPUT Clause (Transact-SQL)](http://msdn.microsoft.com/en-us/library/ms177564.aspx) can do it in a single command, see my answer...
KM
2010-05-13 19:24:11
A:
No, you are pretty much stuck with insert and delete wrapped inside a transaction
SQLMenace
2010-05-13 19:13:56
OP is using SQL Server 2005, so the [OUTPUT Clause (Transact-SQL)](http://msdn.microsoft.com/en-us/library/ms177564.aspx) can do it in a single command, see my answer...
KM
2010-05-13 19:22:41
+8
A:
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)
KM
2010-05-13 19:14:35
@KM: +1. I was not aware of this. Would you recommend this method over `INSERT` and `DELETE`?
Daniel Vassallo
2010-05-13 19:18:22
I 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.
KM
2010-05-13 19:20:24
@lance, unless you have other database changes that you want to tie to this command, then no.
KM
2010-05-13 19:27:47
@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.
KM
2010-05-13 20:12:20
I'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.
lance
2010-05-13 21:06:58
A:
You can try Insert into abc (a,b,c) select(a,b,c) from def
doing above so will insert column a, b,c of def into column a,b,c of abc. after inserting run a delete table, drop table or truncate whatever is your criteria.
sample is:
Begin
Begin try
Begin Transaction
Insert into emp(name, department, salary)
Select empName,empDepartment,empSal from employees
Where employees.empID = 211
Truncate table employees
End Transaction
End try
Begin Catch
if @@Error > 0
Rollback Transaction
End Catch
End
Amit Ranjan
2010-05-13 19:39:32