Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting value for identity column in a temporary table Expand / Collapse
Author
Message
Posted Monday, December 14, 2009 9:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:55 AM
Points: 80, Visits: 189
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?
Post #833925
Posted Monday, December 14, 2009 9:20 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:58 AM
Points: 1,460, Visits: 3,008
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
Post #833934
Posted Monday, December 14, 2009 9:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:55 AM
Points: 80, Visits: 189
I just realized that.

Thanks for the help.
Post #833941
Posted Monday, December 14, 2009 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #833960
Posted Sunday, July 6, 2014 10:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:41 AM
Points: 3, Visits: 33
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.
Post #1589727
Posted Monday, July 7, 2014 12:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:16 AM
Points: 752, Visits: 1,323
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
Post #1589736
Posted Monday, July 7, 2014 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1589779
Posted Monday, July 7, 2014 4:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
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
Post #1590150
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse