MERGE Statement in SQL Server

Ever had to merge two tables in SQL? Hard work?
The MERGE statement is only available in SQL Server 2008 (and Oracle and DB2 ;) ), but in the meantime - you can mimic it.

If you ever had a large csv file with a new version of data already contained in a table in SQL, you dreamed of something like this, didn't you:

MERGE t1
USING (SELECT @id AS id, @name1 AS name1) AS t2 ON t1.id = t2.id
WHEN MATCHED
    THEN UPDATE SET t1.name1 = t2.name1
WHEN NOT MATCHED
    THEN INSERT VALUES(@id, @name1)

In other words: if the id of a row in the new version matches one in the old version - update the old row; otherwise - insert a new row.

[Of course we're assuming here that you managed to load the contents of the csv file into a staging table].

Well, if you have the luxury of working with SQL Server 2008, the construct above is exactly what you could use there. Have a look here: Using MERGE statement.


Now, since SQL Server 2008 is not yet used in too many commercial projects ;) - it is often the case that you have to implement such functionality on your own.

According to Alex Kuznetsov (MVP):

In SQL Server 2005 you can use OUTPUT clause of an UPDATE statement

in order to achieve that.

See for yourself:

declare @updated_ids table(id int)

update permanent set d=s.d, comment = 'Modified Row'
output inserted.id into @updated_ids
from permanent p, staging s
where p.id=s.id

insert into permanent
select id, d, 'New Row' from staging where id not in(select id from @updated_ids)
go

Pretty neat!

You can find Alex's whole post here: Mimicking MERGE Statement in SQL 2005.

How to Execute a SQL Script from .NET Code?

The old days of splitting scripts by the GO statement or using osql are over! SMO is here!

It happens from time to time that you need to execute a T-SQL script - e.g. you are writing a database update manager or whatever.

And it's always the old problem of the 'GO' separator, isn't it? ADO.NET just won't swallow it!

The solution was always to either use some external tool (like osql or sqlcmd) to execute the script or to parse the script for all the 'GO'-es. But both those methods were kind of... not perfect.

The good news is - you don't have to worry about it anymore!

If you have a closer look at SQL Server Management Objects (SMO), you will find the following method:

Server.ConnectionContext.ExecuteNonQuery()

which can actually do all the job for you!


If course it is only available in SQL Server 2005. But isn't that what progress is all about - new things have more features than old ones? ;)


You can find the SMO assemblies here:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

if you have SQL Server 2005 installed on your machine.