Larry Rungren
asked on
identity_insert error
This quick script:
/****** Script for SelectTopNRows command from SSMS ******/
set identity_insert [iMIS_MCLE_15TEST].[dbo].[ BM_OnlineP ass_Subscr iption] on
insert into [iMIS_MCLE_15TEST].[dbo].[ BM_OnlineP ass_Subscr iption]
SELECT [id]
,[clientID]
,[startDate]
,[endDate]
,[period]
,[pricePaid]
,[isNewLawyer]
,[isSponsor]
,[isBaseSubscription]
,[optedOut]
,[sizeOfFirmSource]
,[baseAmount]
,[transactionalDiscountAmo unt]
,[priceToPayYear]
,[initialpurchdate]
,[monthlyamount]
,[lastpayment]
,[lastpaydate]
,[isPromotion]
,[isAutoEnrolled]
,[otherDiscountAmount]
,[isSoldThroughAdmin]
,[periodNextYear]
,[subscriptionStatus]
,[renewalFlag]
FROM [iMIS_MCLE_Prod].[dbo].[BM _OnlinePas s_Subscrip tion]
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_O nlinePass_ Subscripti on' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Why?
/****** Script for SelectTopNRows command from SSMS ******/
set identity_insert [iMIS_MCLE_15TEST].[dbo].[
insert into [iMIS_MCLE_15TEST].[dbo].[
SELECT [id]
,[clientID]
,[startDate]
,[endDate]
,[period]
,[pricePaid]
,[isNewLawyer]
,[isSponsor]
,[isBaseSubscription]
,[optedOut]
,[sizeOfFirmSource]
,[baseAmount]
,[transactionalDiscountAmo
,[priceToPayYear]
,[initialpurchdate]
,[monthlyamount]
,[lastpayment]
,[lastpaydate]
,[isPromotion]
,[isAutoEnrolled]
,[otherDiscountAmount]
,[isSoldThroughAdmin]
,[periodNextYear]
,[subscriptionStatus]
,[renewalFlag]
FROM [iMIS_MCLE_Prod].[dbo].[BM
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_O
Why?
ASKER
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'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'on'.
<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.
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.
ASKER
/****** Script for SelectTopNRows command from SSMS ******/
set identity_insert [iMIS_MCLE_15TEST].[dbo].[ BM_OnlineP ass_Subscr iption] on
go
insert into [iMIS_MCLE_15TEST].[dbo].[ BM_OnlineP ass_Subscr iption]
SELECT [ID]
,[clientID]
,[startDate]
,[endDate]
,[period]
,[pricePaid]
,[isNewLawyer]
,[isSponsor]
,[isBaseSubscription]
,[optedOut]
,[sizeOfFirmSource]
,[baseAmount]
,[transactionalDiscountAmo unt]
,[priceToPayYear]
,[initialpurchdate]
,[monthlyamount]
,[lastpayment]
,[lastpaydate]
,[isPromotion]
,[isAutoEnrolled]
,[otherDiscountAmount]
,[isSoldThroughAdmin]
,[periodNextYear]
,[subscriptionStatus]
,[renewalFlag]
FROM [iMIS_MCLE_Prod].[dbo].[BM _OnlinePas s_Subscrip tion]
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_O nlinePass_ Subscripti on' can only be specified when a column list is used and IDENTITY_INSERT is ON.
set identity_insert [iMIS_MCLE_15TEST].[dbo].[
go
insert into [iMIS_MCLE_15TEST].[dbo].[
SELECT [ID]
,[clientID]
,[startDate]
,[endDate]
,[period]
,[pricePaid]
,[isNewLawyer]
,[isSponsor]
,[isBaseSubscription]
,[optedOut]
,[sizeOfFirmSource]
,[baseAmount]
,[transactionalDiscountAmo
,[priceToPayYear]
,[initialpurchdate]
,[monthlyamount]
,[lastpayment]
,[lastpaydate]
,[isPromotion]
,[isAutoEnrolled]
,[otherDiscountAmount]
,[isSoldThroughAdmin]
,[periodNextYear]
,[subscriptionStatus]
,[renewalFlag]
FROM [iMIS_MCLE_Prod].[dbo].[BM
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_O
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
Thanks for the grade. Good luck with your project. -Jim
The only exeption is to...
Open in new window
<edit>
Hmm.. try losing the table name in the IDENTITY_INSERT statement