How to insert multiple rows into a table with identity column

Question

How to insert multiple rows into a table with identity column

deep_kkumar

Hall of Fame

Points: 3243
More actions
January 4, 2012 at 8:39 am

#252987

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

Viewing 15 posts - 1 through 14 (of 14 total)

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

Richard Moore-400646 SSCrazy Points: 2384 More actions · Answer 1

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 SSC Eights! Points: 858 More actions · Answer 2

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 Mr or Mrs. 500 Points: 517 More actions · Answer 3

Identity coloumn will be always auto gaenerated no need to insert the data for those column .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC Guru Points: 76985 More actions · Answer 5

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

Paul White SSC Guru Points: 150467 More actions · Answer 6

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. 😛

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
SQLkiwi blog
@SQL_Kiwi

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 7

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 9

Ha, ha, ha! "...warm and cuddly...". I like that.

I was going to say "rude", but I choose the word "abrasive" instead.

I am all for learning, and for helping others learn for themselves, as opposed to just providing answers, which is what a lot of the posters here want, but there's no need for insults. Like you, I feel that it would do more harm than good.

But, hey...to each his own, right?!

nigel. SSChampion Points: 11636 More actions · Answer 10

GilaMonster (1/12/2012)
... Personally I think it just drives them away ...

And it looks he's succeeded! No sign of the OP :crying:

Nigel
How to post questions

Paul White SSC Guru Points: 150467 More actions · Answer 11

nigel. (1/13/2012)
And it looks he's succeeded! No sign of the OP :crying:

The simple question was answered long before Joe posted. It seems the questioner has his or her answer, but just hasn't returned to say so.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

CELKO (1/5/2012)
This is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code.
Back to foundation. SQL is a set-oriented language. When you do an INSERT you put in a set, all at once. That set can be zero or more rows.
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?
Please read a book on RDBMS; your mindset is still in magnetic tape files and not RDBMS yet.

Actually, good programmers use this feature all the time and for good reason. Please see the following video...

http://technet.microsoft.com/en-us/sqlserver/Video/gg508879

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 13

CELKO (1/5/2012)
This is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code.
Back to foundation. SQL is a set-oriented language. When you do an INSERT you put in a set, all at once. That set can be zero or more rows.
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?
Please read a book on RDBMS; your mindset is still in magnetic tape files and not RDBMS yet.

This confuses logical design with physical implementation. Certainly, meaningless numeric 'tuple identifiers' are of little use in a purely relational logical design, and many would consider their use to violate some normal form or another. That's irrelevant when we come to implement the logical design though: physical identifiers are frequently an important physical optimization. Also, SQL Server is not a pure relational RDBMS, and nor is SQL a set-orientated language: it has bag semantics, not set semantics.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

gvreddy04 Valued Member Points: 65 More actions · Answer 14

Solution:

1) Set IDENTITY_INSERT to ON.

2) Insert customers data into Customer table.

3) Set IDENTITY_INSERT to OFF.

Read full article here.