SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to insert multiple rows into a table with identity column


How to insert multiple rows into a table with identity column

Author
Message
deep_kkumar
deep_kkumar
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 756
Hi,
I am having trouble inserting the multiple rows into a table with identity column. The error i get is...

An explicit value for the identity column in table can only be specified when a column list is used and identity insert is ON
Richard Moore-400646
Richard Moore-400646
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 1640
It always helps if you include your table and the code you're using so we can see the actual issue. However, it appears that you are inserting into a table that has an identity column and you're trying specify a value. By default identity columns are automatically updated and you can't manually set the value unless you set the IDENTITY_INSERT on for the table you're trying to update.
mhike2hale
mhike2hale
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 157
Do not include your identity column to you insert statement.

insert into myTable(identitycol, col1, col2, coln)
values(1, 'value 1', 'value 2', 'value n') ===>WRONG!

insert into myTable(col1, col2, coln)
values('value 1', 'value 2', 'value n') ===>RIGHT!

"Often speak with code not with word,
A simple solution for a simple question"
raghu-sql
raghu-sql
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 92
Identity coloumn will be always auto gaenerated no need to insert the data for those column .
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230284 Visits: 46344
CELKO (1/5/2012)
This is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code.


Blah, blah, blah.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15827 Visits: 11221
CELKO (1/5/2012)
But IDENTITY is a sequential count of insertion attempts. PHYSICAL insertion attempts. Not a LOGICAL concept at all. So how do you number these rows?


You're confusing what IDENTITY is with how it's generated. IDENTITY is a number that is unique for a given table. PERIOD. How it is generated is irrelevant. I don't know anyone that writes code that depends on how an identity column is generated.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36364 Visits: 11361
drew.allen (1/5/2012)
IDENTITY is a number that is unique for a given table. PERIOD.

Not without something to enforce that uniqueness it isn't. :-P


CREATE TABLE #T (id int IDENTITY(1,1) NOT NULL)
INSERT #T DEFAULT VALUES
SELECT * FROM #T
DBCC CHECKIDENT(#T, RESEED, 0)
INSERT #T DEFAULT VALUES
SELECT * FROM #T
SET IDENTITY_INSERT #T ON
INSERT #T (id) VALUES (1)
SET IDENTITY_INSERT #T OFF
SELECT * FROM #T
DROP TABLE #T





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 1686
Man, kind of abrasive, no, Mr. Celko?! I thought that this is supposed to be a supportive forum. I can understand your frustrations, but we can direct people to do research without being so abrasive.

- Chris
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230284 Visits: 46344
CGSJohnson (1/12/2012)
Man, kind of abrasive, no, Mr. Celko?! I thought that this is supposed to be a supportive forum. I can understand your frustrations, but we can direct people to do research without being so abrasive.


Hell, that's positively warm and cuddly compared to some of Joe's comments. Joe holds the opinion that if he insults newcomers it'll motivate them to study and learn to do things properly. Personally I think it just drives them away from possible improvement and results in them not learning and not seeking out learning.

Joe's attitude is quite common on Oracle boards and forums, very rare on SQL Server ones.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


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