Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting value for identity column in a temporary table


Inserting value for identity column in a temporary table

Author
Message
namrata.dhanawade-1143388
namrata.dhanawade-1143388
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 205
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?
Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1553 Visits: 3445
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
namrata.dhanawade-1143388
namrata.dhanawade-1143388
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 205
I just realized that. :-)

Thanks for the help.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8970 Visits: 19020
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
Exploring Recursive CTEs by Example Dwain Camps
kishore1a1216
kishore1a1216
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 42
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.
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2597
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8970 Visits: 19020
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
Exploring Recursive CTEs by Example Dwain Camps
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
You can also insert explicit values into an IDENTITY column by using SET IDENTITY_INSERT [table] 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search