Insert static data in database in SSDT

Question

I want to pre populate my data base table with some defined data in SSDT. Such that insertion data take place only once. I am using SQL 2005 ?? How can I do this ??In SQL 2008 there is MERGE but I am not finding solution in 2005!!!

Answer 1

I was all excited until I saw you used SQL 2005 ;), poor you I feel your pain as up until recently I was working with a SQL 2005 db.

You will need to do something like:

"if not exists (select * from table where col = blah Insert xxx into table "

If you have lots of rows then you could look at doing something like checking one of the rows and doing a bulk insert or openrowset from a cav file -or- use redgate data compare to manage it for you.

Ed

Answer 2

If it's pre-defined, you might be able to get away with saving that out and inserting using BCP or something similar. You can also write the script and call it as part of some sort of "IF NEW" check. However, your best bet for an ongoing script would be to insert into a temp table, then do some sort of EXCEPT or LEFT JOIN to figure out what doesn't exist and update or insert as appropriate.

There's not a really clean way to do it, but it is doable. My concern would be that you may want to consider a separate "New" script and tell people to create the database, then run that script afterwards if they re-created the DB. This would keep the size of your main release scripts a bit more manageable.