SQL Clone
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-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 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.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1930 Visits: 3488
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-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

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

Thanks for the help.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17232 Visits: 19557
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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2483 Visits: 2675
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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17232 Visits: 19557
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-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 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