Solved

allow insert values into table with identity column

Posted on 2001-08-16
8
881 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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