Solved

allow insert values into table with identity column

Posted on 2001-08-16
8
860 Views
Last Modified: 2008-01-09
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
Comment
Question by:frankytee
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:ibro
ID: 6395926
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6396030
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
 
LVL 19

Author Comment

by:frankytee
ID: 6412295
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
 
LVL 19

Author Comment

by:frankytee
ID: 6412324
what is the syntax to remove the IDENTITY property from a column to allow the insert and then add it back in?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 3

Expert Comment

by:ibro
ID: 6412949
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
 
LVL 19

Author Comment

by:frankytee
ID: 6415873
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
 
LVL 3

Accepted Solution

by:
ibro earned 50 total points
ID: 6416459
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
 
LVL 19

Author Comment

by:frankytee
ID: 6420794
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now