Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

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
0
frankytee
Asked:
frankytee
  • 4
  • 3
1 Solution
 
ibroCommented:
Hi frankytee,
 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
frankyteeAuthor Commented:
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!!






0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
frankyteeAuthor Commented:
what is the syntax to remove the IDENTITY property from a column to allow the insert and then add it back in?
0
 
ibroCommented:
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


0
 
frankyteeAuthor Commented:
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.


0
 
ibroCommented:
I doubt there is something that you are not telling me....
try this script and tell me if works:
set nocount on
go
create table  tst1 (id int identity(1,1), val varchar(20) )
go
insert into tst1 (val) values ('val1')
insert into tst1 (val) values ('val2')
insert into tst1 (val) values ('val3')
insert into tst1 (val) values ('val4')
go

create table tst2 (id int identity(1,1), val varchar(20) )
go

set IDENTITY_INSERT tst2 ON
insert into tst2 (id, val) select id, val from tst1
set IDENTITY_INSERT tst2 OFF
go

select * from tst1
select * from tst2
go

if everything is ok, at the end of the scrpit tables tst1 and tst2 should have the same data.
Btw which version of SQL Server do you use? This script shall work for sure on SQL 7 and SQL2K.
0
 
frankyteeAuthor Commented:
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!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now