allow insert values into table with identity column
i'm new to sql server and this is a most fustrating problem. I have several instances of an sql server db. I have extracted a sample of data from 10 tables from a production db and am trying to insert that data into the dev db. i have cleared all records from the dev db so there would be no conflict of id numbers etc.
Using MS Access and ODBC, the problem is I can not insert the ID column itself (keep getting key violation errors, even though the target table is empty!!), sql will only accept the data if i exclude the ID column during the insert. I must retain the values from these id columns because they are referenced by the other child tables that are also being inserted.
I'm trying in vain to find the syntax which will allow the insertion of the ID column, do I have to temporarily disable the identity column on all the tables involved cause that is terribly inefficient.
i've tried
SET IDENTITY_INSERT myTable ON
but it hasnt worked
Using MS Access and ODBC, the problem is I can not insert the ID column itself (keep getting key violation errors, even though the target table is empty!!), sql will only accept the data if i exclude the ID column during the insert. I must retain the values from these id columns because they are referenced by the other child tables that are also being inserted.
I'm trying in vain to find the syntax which will allow the insertion of the ID column, do I have to temporarily disable the identity column on all the tables involved cause that is terribly inefficient.
i've tried
SET IDENTITY_INSERT myTable ON
but it hasnt worked
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
From access you can't fill the identity fields at all, and the command you showed will only solve your problem if you use SQL statement to copy the data (as ibro suggested)...
CHeers
CHeers
ASKER
there are about 10 tables involved, and some a over 10,000 records so I need to be able to insert from a select statement. If i use
insert into myTable (id, ... ) values (100, ....)
i would then have to write a loop to insert row by row.
Surely there must be a quick solution to this, as this would be a 5 minute job in Access, or this a limitation of the product?
Even when I try to create a table tmpBu (for temporary use) from my permanent table tblBU, using
select tblBu.* into tmpBu from tblBu
the resulting table tmpBu has no records! What is going on here? I was hoping then to switch off the identity property on tmpBu, insert the data from Access into tmpBu, and then insert tmpBu's data into tblBU. But i can't even get past the first stage!!
insert into myTable (id, ... ) values (100, ....)
i would then have to write a loop to insert row by row.
Surely there must be a quick solution to this, as this would be a 5 minute job in Access, or this a limitation of the product?
Even when I try to create a table tmpBu (for temporary use) from my permanent table tblBU, using
select tblBu.* into tmpBu from tblBu
the resulting table tmpBu has no records! What is going on here? I was hoping then to switch off the identity property on tmpBu, insert the data from Access into tmpBu, and then insert tmpBu's data into tblBU. But i can't even get past the first stage!!
ASKER
what is the syntax to remove the IDENTITY property from a column to allow the insert and then add it back in?
The only command to disable identity field in a table is set IDENTITY_INSERT. In your case you should not use:
select * in tmpBU from tblBU
but
create table tmpBU (id int identity(1,1), ....)
set IDENTITY_INSERT tmpBU ON
insert into tmpBU select * from tblBU
set IDENTITY_INSERT tmpBU OFF
select * in tmpBU from tblBU
but
create table tmpBU (id int identity(1,1), ....)
set IDENTITY_INSERT tmpBU ON
insert into tmpBU select * from tblBU
set IDENTITY_INSERT tmpBU OFF
ASKER
ibro, i used the sytax as you suggested below.
set IDENTITY_INSERT tmpBU ON
insert into tmpBU select * from tblBU
set IDENTITY_INSERT tmpBU OFF
but i get the error message:
Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'tmpBU' can only be specified when a column list is used and IDENTITY_INSERT is ON.
When expanded the insert statement to include the column list it still didn't work.
set IDENTITY_INSERT tmpBU ON
insert into tmpBU select * from tblBU
set IDENTITY_INSERT tmpBU OFF
but i get the error message:
Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'tmpBU' can only be specified when a column list is used and IDENTITY_INSERT is ON.
When expanded the insert statement to include the column list it still didn't work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ibro, your code above worked (i'm using sql 7). And then I applied (the same code that I used over the last few days) to the tmpBu and tblBu tables and it worked!
Absolutely no idea why it didnt work before, just hope this works next week!
Absolutely no idea why it didnt work before, just hope this works next week!
you got the right command. If you want to specify value for identity column you have to use set identity_insert command. The code for that is
set identity_insert myTable on
insert into myTable (id, ... ) values (100, ....)
set identity_insert myTably off
Your inserts may fail because of some foreign key in this table. So the insertation in the table shall be done in a proper order. Insert first the rows in the tables that have primary key (IDs) and no foreign keys and after that the tables that have forign keys. Be sure that you have inserted rows in the tables that are referenced by other tables foreign keys. Check the contraints (primary and forign keys) in all the tables that you want to fill with data, so you can make a proper order.
hope this helps