CODEWITHSUNDEEP
sqlsql-server
sunk
sunk

Reputation: 320

How to insert sequential numbers in primary key using select subquery?

I am reading a table A and inserting the date in Table B (both tables are of same structure except primary key data type). In Table B, Primary key is int whereas in Table A it is UniqueIdentifier.

INSERT INTO TableB (ID, Names, Address) (select ID, Names, Address from TableA)

Now how can i insert int type incremental value (1,2,3,so on) in TableB instead of uniqueidentifier from TableA using above script.

Help?

Upvotes: 1

Views: 6498

Answers (4)

Ralph Shillington
Ralph Shillington

Reputation: 21098

If changing the schema of your TableB is not an option then add a rank to your select statement like this:

insert into tableB select rank() over(order by id), name, address from tableA

This will always start at 1. I you could add + 10 if you wanted to start your numbering at a number other than 1. I'm sure you get the idea from there.

Upvotes: 2

Amy B
Amy B

Reputation: 110151

CREATE TABLE TableB
(
  ID int PRIMARY KEY IDENTITY(1,1),
  Name nvarchar(200),
  Address nvarchar(200)
)

Then, in the query, don't specify the value of the identity column.

INSERT INTO TableB(Name, Address)
SELECT Name, Address FROM TableA

Upvotes: 0

Sam
Sam

Reputation: 348

Go to the table properties, select the ID field, under "Identity specification", set "Identity Increment" = 1, "Identity Seed" = 1. By doing that, the ID becomes auto incremental...

Then your insert statement would be something like:

INSERT INTO TableB (Names, Address) (select Names, Address from TableA)

Upvotes: 2

casperOne
casperOne

Reputation: 74540

Why not change Table B so that the primary key is an identity which auto-increments?

Upvotes: 6

Related Questions