Improve company productivity with a Business Account.Sign Up

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

identity_insert error

This quick script:

/****** Script for SelectTopNRows command from SSMS  ******/
set identity_insert [iMIS_MCLE_15TEST].[dbo].[BM_OnlinePass_Subscription] on
insert into [iMIS_MCLE_15TEST].[dbo].[BM_OnlinePass_Subscription]
SELECT [id]
      ,[clientID]
      ,[startDate]
      ,[endDate]
      ,[period]
      ,[pricePaid]
      ,[isNewLawyer]
      ,[isSponsor]
      ,[isBaseSubscription]
      ,[optedOut]
      ,[sizeOfFirmSource]
      ,[baseAmount]
      ,[transactionalDiscountAmount]
      ,[priceToPayYear]
      ,[initialpurchdate]
      ,[monthlyamount]
      ,[lastpayment]
      ,[lastpaydate]
      ,[isPromotion]
      ,[isAutoEnrolled]
      ,[otherDiscountAmount]
      ,[isSoldThroughAdmin]
      ,[periodNextYear]
      ,[subscriptionStatus]
      ,[renewalFlag]
  FROM [iMIS_MCLE_Prod].[dbo].[BM_OnlinePass_Subscription]
  where lastpaydate >= '11/01/2013' and lastpaydate <= '11/30/2013'



generates the following error:

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'iMIS_MCLE_15TEST.dbo.BM_OnlinePass_Subscription' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Why?
0
Jeff_Kingston
Asked:
Jeff_Kingston
  • 4
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
One of these columns, probably id, is an identity field, which means you can't intentionally insert a value into it.

The only exeption is to...
SET IDENTITY_INSERT ON
-- Do your insert here, so it doesn't duplicate rows for the identity field
SET IDENTITY_INSERT OFF

Open in new window


<edit>

Hmm.. try losing the table name in the IDENTITY_INSERT statement
0
 
Jeff_KingstonAuthor Commented:
the syntax above with no table name generates the following error

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'on'.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<wild guess>
Looking at the technet help page, the table name is there, but there's also a GO after the SET IDENTITY_INSERT line(s) that are not in the above code.  Try that.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Jeff_KingstonAuthor Commented:
/****** Script for SelectTopNRows command from SSMS  ******/
set identity_insert [iMIS_MCLE_15TEST].[dbo].[BM_OnlinePass_Subscription] on
go
insert into [iMIS_MCLE_15TEST].[dbo].[BM_OnlinePass_Subscription]
SELECT [ID]
        ,[clientID]
      ,[startDate]
      ,[endDate]
      ,[period]
      ,[pricePaid]
      ,[isNewLawyer]
      ,[isSponsor]
      ,[isBaseSubscription]
      ,[optedOut]
      ,[sizeOfFirmSource]
      ,[baseAmount]
      ,[transactionalDiscountAmount]
      ,[priceToPayYear]
      ,[initialpurchdate]
      ,[monthlyamount]
      ,[lastpayment]
      ,[lastpaydate]
      ,[isPromotion]
      ,[isAutoEnrolled]
      ,[otherDiscountAmount]
      ,[isSoldThroughAdmin]
      ,[periodNextYear]
      ,[subscriptionStatus]
      ,[renewalFlag]
  FROM [iMIS_MCLE_Prod].[dbo].[BM_OnlinePass_Subscription]
  where lastpaydate >= '11/01/2013' and lastpaydate <= '11/30/2013'

Still generates this error:::::(

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'iMIS_MCLE_15TEST.dbo.BM_OnlinePass_Subscription' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>can only be specified when a column list is used
Wonder if this means that your INSERT INTO line must explicity spell out all column names.
This is a good programming practice anyways.
insert into [iMIS_MCLE_15TEST].[dbo].[BM_OnlinePass_Subscription] ([ID] ,[clientID],[startDate], etc, etc. [renewalFlag])
SELECT ... 

Open in new window

btw, in case it's not obvious, you can lose the database and schema references if this is executed in the same database and the default schema for the user is dbo.   You can also lose the square brackets round every name, as there's no characters in these names such as space, dash, $, etc. that warrants using them.
0
 
Jeff_KingstonAuthor Commented:
Duhhhhh!  Kudos for seing my blindness,  of course we need the list of fields as part of the insert statement not the select.   A simple select * from  does the job.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Don't be too pissed at yourself.  I actually got burned on this on the 70-433 SQL 2008 development exam, as the question was 'How to insert rows into a table using as little code as possible, which means not spelling out the INSERT INTO columns, despite the fact that it's considered a poor programming practice and no one I know does this.

Thanks for the grade.  Good luck with your project.  -Jim
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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