[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3256
  • Last Modified:

An explicit value for the identity column in table 'dbTest.dbo.table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I have an insert from one table to another and I keep getting this error.  The destination table has a autoincrementing column and I need it to continue that count.  PLease help.
0
rxresults
Asked:
rxresults
4 Solutions
 
Steve WalesSenior Database AdministratorCommented:
What's the insert statement / table definition look like ?

Sounds like you're trying to set the value of the identity column yourself ?

If you're wanting the identity column to do it's thing and just auto increment, don't specify that column in the insert statement.
0
 
lcohanDatabase AnalystCommented:
ONLY if you DONT need to keep the IDENTITY sequence and MUST insert specific values you can run a SET like below otherwise code your INSERT statement WITHOUT the identity column in the list:

SET IDENTITY_INSERT Table1 ON

against that table and tur it OFF after the insert. More details at:

http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity
0
 
rxresultsAuthor Commented:
Here is what I am using and I still get the error: The primary key (which is also the autoincrementing column) is missing from this query


set identity_Insert 'dbTest.dbo.table'  on
go
insert into  'dbTest.dbo.table'
SELECT
       [ValidRx] as [ValidRx]
      ,[ImportOrgId] as [ImportOrgId]
      ,[CustomerId] as [CustomerId]
      ,[AccountId] as [AccountId]
      ,[GroupId] as [GroupId]
      ,[UtilizationDate] as [UtilizationDate]
      ,[ProcessDate] as [ProcessDate]
   FROM 'dbTest.dbo.table'
  go
 
  set identity_Insert 'dbTest.dbo.table'  off
go
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Scott PletcherSenior DBACommented:
Re-read the error message carefully:

"when a column list is used and ..."


set identity_Insert dbTest.dbo.table  on
go
insert into  dbTest.dbo.table (
   [ValidRx], [ImportOrgId], [CustomerId], ...  --<<-- as msg states, you must list columns
)
SELECT
    ...
0
 
Steve WalesSenior Database AdministratorCommented:
Try specifying the column names in your insert rather than allowing them to default ?

insert into table (col1, col2, col3) select (cola, colb, colc)

Does that make any difference ?
0
 
rxresultsAuthor Commented:
I tried it and now the message has changed slightly:

Explicit value must be specified for identity column in table 'dbo.table'  either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
0
 
Steve WalesSenior Database AdministratorCommented:
Don't turn IDENTITY_INSERT ON when you're trying to have the identity do it's job automatically.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now