insert problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert problems

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-22 : 16:47:47

I am running a simple insert.....

Insert INTO Defaults (DefaultID, ProgramID, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate)
(select DefaultID, ProgramID+8669, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate
From rating_2.dbo.defaults
Where ProgramID Between 1331 and 1420)

and I have recieved this error....

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Defaults' when IDENTITY_INSERT is set to OFF.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-22 : 16:56:40

Is this a one-time script? If so, then use the SET IDENTITY_INSERT option. If this isn't a one-time script, then you need to consider not using the identity value for something that you'll be providing the value for.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-23 : 09:11:43

All I want to do is copy all the information and set it with a new programid that is 8669 higher then the one I am copying.

How would I change this to a Set Identity_Insert and what would that do? Thanks for the help!

Best,
GG

Insert INTO Defaults (DefaultID, ProgramID, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate)
(select DefaultID, ProgramID+8669, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate
From rating_2.dbo.defaults
Where ProgramID Between 1331 and 1420)

Insert INTO Defaults (DefaultID, ProgramID, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate)
(select DefaultID, ProgramID+90, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate
From rating_2.dbo.defaults
Where ProgramID Between 10000 and 10089)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 09:20:53

set identity_insert Defaults on
Insert INTO Defaults (DefaultID, ProgramID, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate)
(select DefaultID, ProgramID+8669, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate
From rating_2.dbo.defaults
Where ProgramID Between 1331 and 1420)

Insert INTO Defaults (DefaultID, ProgramID, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate)
(select DefaultID, ProgramID+90, CoverageID, InclAmt, CalcType, MinLimit, LimitID, Territory, RateEffectiveDate, RateExpirationDate
From rating_2.dbo.defaults
Where ProgramID Between 10000 and 10089)
set identity_insert Defaults off

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-23 : 09:26:30

Thank you.

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-23 : 09:33:41

I recieved this error. Also I did not have to do somthing like that before.

say i have ten rows of data each with a unique programid 1-10. What I want is that data copied in that table and then have the programid change to 30 -31. so I now have programid 1-10, and 30-31 that have the exact same data just different programids.......

thanks for the help.

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK_Defaults'. Cannot insert duplicate key in object 'dbo.Defaults'.
The statement has been terminated.

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-23 : 09:40:45

I figured it out! thanks for all your help.

Best,
GG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources