Move row from one table to another?

Question

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

KM. · Accepted Answer · 2010-05-13T20:32:18.190

32

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)

edited May 13 '10 at 20:32

answered May 13 '10 at 19:14

KM.

101,727
34
178
212

1

@KM: +1. I was not aware of this. Would you recommend this method over `INSERT` and `DELETE`? – Daniel Vassallo May 13 '10 at 19:18
1

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. May 13 '10 at 19:20
This is one statement, so... no transaction necessary, I guess? – lance May 13 '10 at 19:22
@lance, unless you have other database changes that you want to tie to this command, then no. – KM. May 13 '10 at 19:27
@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. May 13 '10 at 20:12
3

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 May 13 '10 at 21:06
@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? – joshmax Oct 07 '11 at 21:52
2

@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. – KM. Oct 10 '11 at 15:07

score 3 · Answer 2 · answered May 13 '10 at 19:39

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

score 0 · Answer 3 · answered May 13 '10 at 19:13

0

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.

answered May 13 '10 at 19:13

JonH

32,732
12
87
145

5

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. May 13 '10 at 19:24

score 0 · Answer 4 · answered May 13 '10 at 19:13

0

No, you are pretty much stuck with insert and delete wrapped inside a transaction

answered May 13 '10 at 19:13

SQLMenace

132,095
25
206
225

4

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. May 13 '10 at 19:22

score 0 · Answer 5 · edited Jul 23 '15 at 14:23

INSERT dbo.newtable(
      name,
      department,
      Salary
) SELECT 
            name,
            FirstName,
            Lastname
      FROM    (
           DELETE dbo.oldtable
           OUTPUT
                   DELETED.name,
                   DELETED.department,
                   DELETED.Salary
           WHERE ID  IN ( 1001, 1003, 1005 )
      ) AS RowsToMove  

SELECT * FROM dbo.newtable
SELECT * FROM dbo.oldtable

Move row from one table to another?

5 Answers5

Linked