insert into table with ID column
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.
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
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.
精彩评论