CODEWITHSUNDEEP
sqlsql-server-2008-r2copy
Nale
Nale

Reputation: 11

SQL copy from one table to another with changing ID value

I have two tables:

A

ID   VALUE
----------
1      7
2      5
3      44
4      982
5      1
6      0
7      671

B

ID     VALUE
---------------
1       6
2       6
3       77
4       22

How do I copy data from #B to #A to get a different ID (one bigger than the MAX in #A)? For example I need to get

ID   VALUE
1      7
2      5
3      44
4      982
5      1
6      0
7      671
8       6
9       6
10      77
11      22

Upvotes: 0

Views: 187

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

If the ID column in TableA is not already set to auto-increment, do the following command:

ALTER TABLE TableA MODIFY COLUMN ID INT auto_increment

Now you can just insert all the records from TableB into TableA:

INSERT INTO TableA (VALUE)
SELECT VALUE
FROM TableB

It is not a great idea to rely on the business logic in your query to maintain the order of the ID column. Instead, let SQL take care of it for you; it was designed for this purpose.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460068

Either make it an IDENTITY column which auto-increments, or this:

INSERT INTO A
   SELECT b.ID + (SELECT MAX(ID) FROM A) AS ID, b.Value
   FROM B

DEMO

The select is slightly different if the ID in table B has gaps. Then those gaps are transferred.

Upvotes: 1

Related Questions