How to run a Insert data Script in SQL Server with same id available in SQL DB

Question

0.00/5 (No votes)

See more:

SQL

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

avisatna

Add a Solution

Comments

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

3 solutions

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!
:)

Posted 11-Oct-12 19:35pm

Aarti Meswania

Comments

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

Solution 3

Thanks to all I solved it myself....

Thanks for your reply...

Posted 11-Oct-12 20:36pm

avisatna

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Afzal Shaikh · Accepted Answer · 2012-10-11T19:28:00

Before inserting execute the below statement for each table.

for example:

SQL

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

SQL

EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"