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.
rxresultsAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior 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 WalesConnect With a Mentor Senior 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
 
lcohanConnect With a Mentor Database 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
Steve WalesConnect With a Mentor Senior 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.