CODEWITHSUNDEEP
sql-server
Nickolodeon
Nickolodeon

Reputation: 2956

mssql script data insert or update

How can I generate script instead of manually writing 

if exists (select ... where id = 1)
insert ...
else
update ...

Very boring to do that with many records!

Using management studio to generate script 'Data only' generates only inserts. So running that against existing db gives error on primary keys.

Upvotes: 1

Views: 909

Answers (2)

Kamil Nowinski
Kamil Nowinski

Reputation: 545

MERGE is one of the most effective methods to do this.
However, writing a Merge statement is not very intuitive at the beginning, and generating lines for many rows or many tables is a time-consuming process.
I'd suggest using one of the tools to simplify this challenge:

I wrote a blog post about these tools recently and approach to leveraging SSDT for deployment database with data. Find out more:
Script and deploy the data for database from SSDT project

I hope this can help.

Upvotes: 1

Jamiec
Jamiec

Reputation: 136114

For SQL 2008 onwards you could start using Merge statements along with a CTE

A simple example for a typical id/description lookup table

WITH stuffToPopulate(Id, Description)
AS
(
   SELECT 1, 'Foo'
   UNION SELECT 2, 'Bar'
   UNION SELECT 3, 'Baz'
)
MERGE Your.TableName AS target
USING stuffToPopulate as source
ON (target.Id = source.Id)
WHEN MATCHED THEN 
    UPDATE SET Description=source.Description

WHEN NOT MATCHED THEN
    INSERT (Id, Description)
    VALUES (source.Id, source.Description);

Merge statements have a bunch of other functionality that is useful (such as NOT MATCHED BY DESTINATION, NOT MATCHED BY SOURCE). The docs (linked above) will give you much more info.

Upvotes: 3

Related Questions