 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008keep identity on inserts
I exported some data into a clean table and I got an errror, something about it couldn't reset the primary key values because KEEPIDENTITY was turned off. So I did the export over and I tried to turn KEEPIDENTITY on so that I could use the key values from the export instead of the auto-enter primary key value however I got an error message that said it was invalid on all the insert rows. How can I fix the inserts to keep the values of the export. INSERT INTO INTERNAL_SPLC with (KEEPIDENTITY) (splc_id, splc, city, state) VALUES(684, '702138000', 'BOWDOIN', 'MT'); INSERT INTO INTERNAL_SPLC with (KEEPIDENTITY) (splc_id, splc, city, state) VALUES(685, '683452000', 'BUCKHOLTS', 'TX'); INSERT INTO INTERNAL_SPLC with (KEEPIDENTITY) (splc_id, splc, city, state) VALUES(686, '588950000', 'WELLINGTO', 'KS'); Msg 8171, Level 16, State 1, Line 1 Hint 'keepidentity' on object 'INTERNAL_SPLC' is invalid tia, Look in Books Online for SET IDENTITY_INSERT ON/OFF  SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net >I exported some data into a clean table and I got an errror, something > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 14 lines] > Hint 'keepidentity' on object 'INTERNAL_SPLC' is invalid > tia, > Look in Books Online for SET IDENTITY_INSERT ON/OFF > [quoted text clipped - 22 lines] > > Hint 'keepidentity' on object 'INTERNAL_SPLC' is invalid > > tia, I did try books on-line but I'm not sure I got the right place, it gave this as an example: USE AdventureWorks; GO DELETE HumanResources.myDepartment; GO INSERT INTO HumanResources.myDepartment with (KEEPIDENTITY) (DepartmentID, Name, GroupName, ModifiedDate) SELECT * FROM OPENROWSET(BULK 'C:\myDepartment-n.Dat', FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1; GO , do but I did not know what the SELECT * FROM OPENROWSET( BULK was in reference to > Look in Books Online for SET IDENTITY_INSERT ON/OFF > [quoted text clipped - 22 lines] > > Hint 'keepidentity' on object 'INTERNAL_SPLC' is invalid > > tia, thanks, I found the right reference Don't forget to turn it back off when you are done. ;) SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net >> Look in Books Online for SET IDENTITY_INSERT ON/OFF >> [quoted text clipped - 24 lines] > > thanks, I found the right reference >I exported some data into a clean table and I got an errror, something > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 14 lines] > Hint 'keepidentity' on object 'INTERNAL_SPLC' is invalid > tia, From BOL: KEEPIDENTITY Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET. ... For an example that uses this hint in an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, see Keeping Identity Values When Bulk Importing Data. Drop the table hint and use SET IDENTITY_INSERT ON SET IDENTITY_INSERT INTERNAL_SPLC ON GO INSERT INTO INTERNAL_SPLC (splc_id, splc, city, state) VALUES(684, '702138000', 'BOWDOIN', 'MT'); INSERT INTO INTERNAL_SPLC /(splc_id, splc, city, state) VALUES(685, '683452000', 'BUCKHOLTS', 'TX'); INSERT INTO INTERNAL_SPLC (splc_id, splc, city, state) VALUES(686, '588950000', 'WELLINGTO', 'KS'); GO SET IDENTITY_INSERT INTERNAL_SPLC OFF GO John > >I exported some data into a clean table and I got an errror, something > > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 21 lines] > OPENROWSET. > ... Okay, I got it with the SET IDENTITY swtich. THANKS, > >I exported some data into a clean table and I got an errror, something > > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 41 lines] > > John I thought it worked but it doesn't. Here is an example of my DROP,CREATE, AND INSERT and the error message. -- -- Table structure for table[INTERNAL_SPLC] -- DROP TABLE INTERNAL_SPLC; CREATE TABLE[INTERNAL_SPLC]( [splc_id]int NOT NULL identity(1,1), [splc]varchar(10)default NULL, [city]varchar(20) NOT NULL, [state]char(2) NOT NULL, PRIMARY KEY (splc_id) ) ; SET IDENTITY_INSERT dbo.INTERNAL_SPLC ON; INSERT INTO INTERNAL_SPLC (splc_id, splc, city, state) VALUES(1, '384850000', 'OTTAWA', 'IL'); INSERT INTO INTERNAL_SPLC (splc_id, splc, city, state) VALUES(65, '565115000', 'LAPLATA', 'MO'); sg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'INTERNAL_SPLC' when IDENTITY_INSERT is set to OFF. Msg 544, Level 16, State 1, Line 2 Cannot insert explicit value for identity column in table 'INTERNAL_SPLC' when IDENTITY_INSERT is set to OFF. That should work, it certainly works for me (when my default schema is dbo). The only problem that I see that you might have is that you are dropping and creating and inserting into a table named [INTERNAL_SPLC], however, you are setting IDENTITY_INSERT ON for dbo.INTERNAL_SPLC. If you have a default schema other than dbo, you are not inserting into the table you are setting IDENTITY_INSERT for. Tom <snip> >> John > I thought it worked but it doesn't. Here is an example of my [quoted text clipped - 25 lines] > Cannot insert explicit value for identity column in table > 'INTERNAL_SPLC' when IDENTITY_INSERT is set to OFF. > SET IDENTITY_INSERT dbo.INTERNAL_SPLC ON; > [quoted text clipped - 9 lines] > Cannot insert explicit value for identity column in table > 'INTERNAL_SPLC' when IDENTITY_INSERT is set to OFF. Note that you are missing a GO between the the SET and INSERT. The INSERT (requiring the IDENTITY_INSERT ON) couldn't be compiled because the SET command was not yet executed. You need to issue the SET command in a separate batch like John's example. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >> >I exported some data into a clean table and I got an errror, something >> > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 70 lines] > Cannot insert explicit value for identity column in table > 'INTERNAL_SPLC' when IDENTITY_INSERT is set to OFF. On Jul 1, 9:31 pm, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> wrote: > > SET IDENTITY_INSERT dbo.INTERNAL_SPLC ON; > [quoted text clipped - 14 lines] > command was not yet executed. You need to issue the SET command in a > separate batch like John's example. Hi Dan, I have hundreds of scripts with SET IDENTITY_INSERT ON and the inserts themselves in the same batch, and all run OK in 2005. I think that either: 1. there is another table INTERNAL_SPLC in another schema, so instead of INSERT INTO INTERNAL_SPLC (splc_id, splc, city, state) VALUES should be INSERT INTO dbo.INTERNAL_SPLC (splc_id, splc, city, state) VALUES 2. or that the SET IDENTITY_INSERT ON failed, because it was already set on for another table. What do you think? > I think that either:hat > [quoted text clipped - 11 lines] > > What do you think? I agree with your analysis, Alex. I was thinking that this might have been a SQL 2000/SQL 2005 behavior difference but I was able to run all statements within the same batch under SQL 2000 without issues. I didn't have a SQL 2000 instance handy when I initially responded. That's what I get for trusting my memory ;-(. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > On Jul 1, 9:31 pm, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> > wrote: [quoted text clipped - 38 lines] > > What do you think? >> >I exported some data into a clean table and I got an errror, something >> > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 70 lines] > Cannot insert explicit value for identity column in table > 'INTERNAL_SPLC' when IDENTITY_INSERT is set to OFF. Hi Janis You should be ok with the given SQL, what you have not told us is which version of SQL Server you are running (SELECT @@VERSION) The following makes sure you have the correct schemas. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INTERNAL_SPLC]') AND type in (N'U')) DROP TABLE [dbo].[INTERNAL_SPLC] GO CREATE TABLE dbo.[INTERNAL_SPLC]( [splc_id]int NOT NULL identity(1,1), [splc]varchar(10)default NULL, [city]varchar(20) NOT NULL, [state]char(2) NOT NULL, CONSTRAINT pk_internal_splc PRIMARY KEY (splc_id) ) ; SET IDENTITY_INSERT dbo.INTERNAL_SPLC ON; INSERT INTO dbo.INTERNAL_SPLC (splc_id, splc, city, state) VALUES(1, '384850000', 'OTTAWA', 'IL'); INSERT INTO dbo.INTERNAL_SPLC (splc_id, splc, city, state) VALUES(65, '565115000', 'LAPLATA', 'MO'); SET IDENTITY_INSERT dbo.INTERNAL_SPLC OFF; John > >I exported some data into a clean table and I got an errror, something > > about it couldn't reset the primary key values because KEEPIDENTITY [quoted text clipped - 41 lines] > > John I just realized something after a few tries. The INSERTS have to be done at the same time as the SWITCH is set to ON; In other words it doesn't permanently change it to ON only for the one query. Anyway, I think it is working. <snip> > I just realized something after a few tries. The INSERTS have to be > done at the same time as the SWITCH is set to ON; In other words it > doesn't permanently change it to ON only for the one query. Anyway, I > think it is working. That's not correct, the IDENTITY_INSERT stays on until you turn it off. But it is on ONLY for the the connection that did the SET. And procedures and exec's get there own "connection", so if you turn IDENTITY_INSERT ON in a procedure that will have an effect only while that stored procedure is running. Tom |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.