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 12»»

How to insert multiple rows into a table with identity column Expand / Collapse
Author
Message
Posted Wednesday, January 4, 2012 8:39 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:13 PM
Points: 124, Visits: 750
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
Post #1230002
Posted Wednesday, January 4, 2012 8:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:57 PM
Points: 199, Visits: 1,489
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.
Post #1230021
Posted Thursday, January 5, 2012 1:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 7:54 PM
Points: 56, Visits: 156
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"
Post #1230532
Posted Thursday, January 5, 2012 5:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 4, 2013 10:42 AM
Points: 219, Visits: 92
Identity coloumn will be always auto gaenerated no need to insert the data for those column .
Post #1230638
Posted Thursday, January 5, 2012 11:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 1,945, Visits: 2,862
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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1231044
Posted Thursday, January 5, 2012 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1231063
Posted Thursday, January 5, 2012 12:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1231071
Posted Wednesday, January 11, 2012 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234378
Posted Thursday, January 12, 2012 6:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 1,546, Visits: 1,334
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
Post #1234728
Posted Thursday, January 12, 2012 6:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1234747
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse