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

Question

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?

Answer 1

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

Answer 2

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)

Answer 3

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

Answer 4

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.