Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
I have a Insert Data Script with IDs.... and in SQL database have some records in same table....
 
when i run Insert data script there is problem in inserting Insert Data script record in same table because SQL db table and my Insert data script have same IDs
 

So how to resolve that problem....
Posted 11-Oct-12 19:20pm
Comments
skydger at 12-Oct-12 1:32am
   
Are there those values with same id's also identical? Anyway you could use EXISTS operator to check those rows before inserting them. Also you can load all the inserts data to tmporary table and then to use MERGE statement, if those rows with the same ids are identical.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Before inserting execute the below statement for each table.
 
for example:
 
SET IDENTITY_INSERT table_name ON
insert into ...
SET IDENTITY_INSERT table_name Off
 
if every table in db has an identity field then you can on identity insert for all tables at a time
 
EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

depends on criteria
if you want to add data with same id,
then do not make your Id column primary key and do not apply unique key.
 
if you want that id should be unique but data must be insert in table with new id.
then make Id column auto increment
and from script remove id column & value for id column from column,value list in script of insert query.
 
Happy coding!
Smile | :)
  Permalink  
Comments
avisatna at 12-Oct-12 1:58am
   
I have more than 1000 lines of Insert Data Script...
so its not possible to remove ID column from Script...
Aarti Meswania at 12-Oct-12 2:01am
   
ok then it will allow same ids in table as mentioned in first paragraph, it is similar like said in solution-1.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Thanks to all I solved it myself....
 

Thanks for your reply...
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100