Tutorial :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?


Solution:1

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


Solution: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)


Solution:3

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.


Solution:4

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »