Migrating an specific row to another table with same columns?

Question

supposing that I have two tables with same 3 columns: ID (int, identity), text (varchar), status (smallint)

By doing a query, how I can transfer the content of a row from Table_A from Table_B?

I.e.:

INSERT INTO TABLE_B VALUES (SELECT * FROM TABLE_A WHERE ID=1) 
 --THIS IS NOT FUNCTIONAL

Here's the catch: That row that was inserted into Table_B also needs to be deleted from Table_A

Thank you.

gbn · Accepted Answer · 2011-05-04T17:28:44.770

4

You need

SET IDENTITY_INSERT to preserve ID
A transaction

Note: SET XACT_ABORT ON forces a ROLLBACK TRAN on error

SET XACT_ABORT ON
BEGIN TRAN

SET IDENTITY_INSERT TABLE_B ON
INSERT TABLEB (ID, text, status)
SELECT ID, text, status FROM TABLE_A WHERE ID=1
SET IDENTITY_INSERT TABLE_B OFF

DELETE TABLE_A WHERE ID=1
COMMIT TRAN

Edit

CREATE PROC dbo.foo
   @ID int
AS
SET NOCOUNT, XACT_ABORT ON
BEGIN TRAN

SET IDENTITY_INSERT TABLE_B ON
INSERT TABLEB (ID, text, status)
SELECT ID, text, status FROM TABLE_A WHERE ID=@ID
SET IDENTITY_INSERT TABLE_B OFF

DELETE TABLE_A WHERE ID=@ID
COMMIT TRAN
GO

edited May 04 '11 at 17:28

answered May 04 '11 at 17:20

gbn

422,506
82
585
676

Nice! How can I make a procedure out of this with a variable set on the ID=@Value ? – Rosenberg May 04 '11 at 17:27

score 2 · Answer 2 · edited May 23 '17 at 11:47

2

Even though you didn't mention what version of sql server you are using... the OUTPUT Clause is probably your best bet (SQL SERVER 2k5 +)

Move row from one table to another?

That will not only take care of your insert, it will also take care of your delete and since its all done at once you don't have to worry about transactions... its probably the safest way to do so in sql server.

EDIT

just to be clear... as far as i know it will only work if your id column does not have to be an identity.... if it does... then a xaction insert/delete is the only safe way to go.

edited May 23 '17 at 11:47

Community

1
1

answered May 04 '11 at 17:20

Patrick

7,512
7
39
50

Yep. There are quite a few restrictions on the target table for an `OUTPUT INTO` but if the OP can meet those this seems neatest. Avoids race conditions etc. – Martin Smith May 04 '11 at 17:22
unless there is some sort of odd requirement on the target table that makes it n/a for OUTPUT i would hope that if they are "two tables with the same 3 columns" that this would work out for the OP. but of course it doesn't always work out that way. – Patrick May 04 '11 at 17:24
I'll disagree because there are IDENTITY columns in the target table (says OP) + still need a DELETE. Otherwise, an OUTPUT clause would be quite elegant and can be used for the required DELETE so only 1 filter is needed – gbn May 04 '11 at 17:24
it would be great if the id didn't HAVE to be an identity... but if it does then your way (@gbn) is the only other way to do it safely. – Patrick May 04 '11 at 17:27
@gbn - Not sure how the `IDENTITY` issue is meant to impact on the appropriateness of this answer? It still seems to work fine as per the script in my answer. – Martin Smith May 04 '11 at 17:37
1

@Martin: I haven't seen or tried IDENTITY with OUTPUT. My initial thought would be "not allowed" as part of restrictions on the OUTPUT target table. However, now I've learnt something new which leads back to my "elegant" comment above – gbn May 04 '11 at 18:10

score 1 · Answer 3 · answered May 04 '11 at 17:34

This works fine

CREATE TABLE Table_A
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[TEXT] VARCHAR(50),
[status] SMALLINT
)

INSERT INTO Table_A VALUES ('foo',1)

CREATE TABLE Table_B
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[TEXT] VARCHAR(50),
[status] SMALLINT
)

SET IDENTITY_INSERT Table_B ON
DELETE FROM Table_A
OUTPUT deleted.* INTO Table_B(ID,[TEXT], [status])
WHERE ID = 1
SET IDENTITY_INSERT Table_B OFF

SELECT * FROM Table_A
SELECT * FROM Table_B

DROP TABLE Table_A
DROP TABLE Table_B

Migrating an specific row to another table with same columns?

3 Answers3