Inserting value for identity column in a temporary table

Question

Inserting value for identity column in a temporary table

namrata.dhanawade-1143388

Ten Centuries

Points: 1269
More actions
December 14, 2009 at 9:14 am

#143675

Hi
I have a temporary table with one of the column as -
RowID int identity(1,1) not null
Now when i want to insert into this table using a Select query, it gives error -
"The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."
Create Table #temp
(RowID int identity(1,1) not null,
MyID int not null,
MyName varchar(50) not null
)
Insert into #temp (RowID, MyID, MyName)
Select 1, 'Name'
select *
from #temp
drop table #temp1
Is there another way of doing this. And why does this not work?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Create Table #temp
(RowID int identity(1,1) not null,
MyID int not null,
MyName varchar(50) not null
)
Insert into #temp (RowID, MyID, MyName)
Select 1, 'Name'

Should read Insert into #temp (MyID, MyName) as you have listed 3 fields to insert but only entered 2 fields, as you are using an identity field you need not enter a value for this.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 2

namrata.dhanawade-1143388

Ten Centuries

Points: 1269

December 14, 2009 at 9:24 am

#1092044

I just realized that. 🙂

Thanks for the help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There's an alternative syntax which you may find more intuitive:

SELECT IDENTITY(int, 1, 1) AS RowID, ...

INTO #temp

FROM ...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kishore1a1216 SSC-Addicted Points: 409 More actions · Answer 4

If the FROM table ALSO have identity Column then what we do ?

I am Facing this Problem ..

SELECT IDENTITY(int, 1, 1) AS 'RowID',* ,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY ID ) AS RN,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY ID)-1 AS RNL

INTO #temp

FROM Temp1

RowId--->Identity IN #temp

ID------->Identity IN Temp1

Here Iam Getting The Error like

Msg 8108, Level 16, State 1, Line 3

Cannot add identity column, using the SELECT INTO statement, to table '#temp', which already has column 'ID' that inherits the identity property.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

you are facing this Issue because of (*). when you do the following :

SELECT * INTO TABLE2 FROM TABLE1.

and an identity column exists in it, that particular identity column will be automatically inherit in TABLE2.

so if you want to avoid this issue, just used the column which you required something like this

SELECT IDENTITY(INT,1,1) AS RowID, Col1,Col2

INTO TABLE2

FROM TABLE1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

kishore1a1216 (7/6/2014)
If the FROM table ALSO have identity Column then what we do ?
I am Facing this Problem ..
SELECT IDENTITY(int, 1, 1) AS 'RowID',* ,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY ID ) AS RN,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY ID)-1 AS RNL
INTO #temp
FROM Temp1
RowId--->Identity IN #temp
ID------->Identity IN Temp1
Here Iam Getting The Error like
Msg 8108, Level 16, State 1, Line 3
Cannot add identity column, using the SELECT INTO statement, to table '#temp', which already has column 'ID' that inherits the identity property.

Remove the identity property by casting:

DROP TABLE #ExistingTable

CREATE TABLE #ExistingTable (ExistingID int identity(1,1), blah VARCHAR(20))

INSERT INTO #ExistingTable (blah) VALUES ('first row'), ('second row'),('third row')

SELECT * FROM #ExistingTable

DROP TABLE #Temp

SELECT IDENTITY(int,1,1) AS ReplacementID, ExistingID = CAST(ExistingID AS INT), blah

INTO #Temp

FROM #ExistingTable

SELECT * FROM #Temp

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

autoexcrement SSCertifiable Points: 5951 More actions · Answer 7

You can also insert explicit values into an IDENTITY column by using SET IDENTITY_INSERT

ON.

http://msdn.microsoft.com/en-us/library/ms188059.aspx

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura