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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1783 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
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: 1590 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
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 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
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

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

Group: General Forum Members
Points: 547237 Visits: 47736
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
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: 36557 Visits: 13619
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 Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79384 Visits: 11400
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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4326 Visits: 1696
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 (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)

Group: General Forum Members
Points: 547237 Visits: 47736
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