1

I am using the SQL Server 2008 Management Studio Generate Scripts... wizard to generate data only scripts for various tables in my database.

The problem is it generates a bunch of insert statements, but doesn't include any kind of if exists logic. So, I'm getting primary key exceptions for the records that already exist.

Does anyone know of a way to script the data in a table such that it checks if the record already exists or not?

Improve this question

2 Answers 2

Reset to default
1

The easiest way to do this is to create a View that only Selects rows with unique values for your new Primary Key, and then Generate a data script from that View.

If you show us the CREATE TABLE script for your source tables and what your new Primary Key is to be, I can show you how to create such a View.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yeah, I understand what you are saying. Was hoping for a wizard that could do it really quickly for me without a lot of extra work. This will be my fallback plan, if I can't find another solution.
0

I guess you meant there are FK errors as some Tables shall require data to be present before it inserts data in the tables as its referenced.

The generate scripts wizard shall give you set of insert scripts but would not sequence it for you, what you need to do is intelligently all the child tables insert scripts should be sequenced earlier than the parent tables, as once the data is populated in Parent table all the referenced table would have the values.

Improve this answer

4 Comments

No, sequence is not an issue, nor are foreign keys. If I run the auto-generate scripts the first time everything is fine. If I run them a second time, everything blows up because the primary keys are already in the table. I want the auto-generated code to put some extra logic to check if rows already exist or not.
So you require if exists() in insert statements to be generated?
That is exactly what I'm trying to do. And trying to do it quickly via a wizard without writing a lot of code. Any ideas or know of any tools?
I really doubt if there's a ready tool which can do the job for you, but i feel youand pass can make a function while passing the tableName and key which will check and let you know. Based on that either you update the row or skip an insert. Will that work? If you want i can write a dummy function for you...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.