Solved

identity_insert error

Posted on 2013-10-24
7
318 Views
Last Modified: 2013-10-24
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
Comment
Question by:Jeff_Kingston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39597123
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
 

Author Comment

by:Jeff_Kingston
ID: 39597196
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39597220
<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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Jeff_Kingston
ID: 39597371
/****** 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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39597404
>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
 

Author Closing Comment

by:Jeff_Kingston
ID: 39597601
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39597616
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question