Solved

allow insert values into table with identity column

Posted on 2001-08-16
8
845 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
Comment Utility
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]
Comment Utility
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
Comment Utility
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
Comment Utility
what is the syntax to remove the IDENTITY property from a column to allow the insert and then add it back in?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Expert Comment

by:ibro
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 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

17 Experts available now in Live!

Get 1:1 Help Now