Mssql script insert or update data

Question

How can I generate a script instead of manually writing

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

It's really boring to do this with many records!

Using a control studio to generate a data-only script generates only inserts. Thus, working on an existing db gives an error in the primary keys.

Answer 1

In SQL Server 2008, you can start using Merge and CTE

A simple example of 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 operators contain many useful functions (for example, NOT MATCHED BY DESTINATION , NOT MATCHED BY SOURCE ). The documents (linked above) will give you much more information.