mssql script data insert or update

Question

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.

Answer 1

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.

Answer 2

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:

• Data Script Writer (Desktop Application for Windows)
• Generate SQL Merge (T-SQL Stored Procedure)

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.