insert into table with ID column

Question

I am attempting to insert columns from a temp table and want the ID column to increment sequentially. I'm using the following code but nothing is getting inserted:

INSERT INTO TestDataTable
SELECT  Code, Description, ParentID  FROM TempTable

Kindly tell me how I can get this to work.

Answer 1

You need to make the ID an IDENTITY column. So in your select into statement, something like this should do:

INSERT INTO TestDataTable
SELECT ID = IDENTITY(INT,1,1), Code, Description, ParentID  FROM TempTable

Answer 2

I would say that you have two options. The first is to handle this in the data definition language of your target table. See this code example written for MS SQL Server.

CREATE TABLE #A (  
  Id    INT NOT NULL IDENTITY(1,1) PRIMARY KEY
, Code  VARCHAR(10) NOT NULL)  

CREATE TABLE #B (
  Code  VARCHAR(10) NOT NULL)  

 INSERT INTO #B VALUES ('alpha')
 INSERT INTO #B VALUES ('beta')
 INSERT INTO #B VALUES ('gamma')
 INSERT INTO #B VALUES ('delta')

 INSERT INTO #A (Code)
 SELECT Code
 FROM   #B

 SELECT *
 FROM   #A

 DROP TABLE #A
 DROP TABLE #B

If you are not allowed to handle in the target table's DDL then you can accomplish what you need be specifying on the way into the table like this if you know it will be empty.

CREATE TABLE #A (
  Id    INT NOT NULL PRIMARY KEY
, Code  VARCHAR(10) NOT NULL)  

CREATE TABLE #B (
  Code  VARCHAR(10) NOT NULL)

 INSERT INTO #B VALUES ('alpha')
 INSERT INTO #B VALUES ('beta')
 INSERT INTO #B VALUES ('gamma')
 INSERT INTO #B VALUES ('delta')

 INSERT INTO #A (Id, Code)
 SELECT ROW_NUMBER() OVER (ORDER BY Code), Code
 FROM   #B

 SELECT *
 FROM   #A

 DROP TABLE #A
 DROP TABLE #B

I definitely recommend the first method. It is much simpler and you don't have to worry about handling the auto-increment in code.