T-SQL 101: 142 Merging data into a SQL Server table

Sometimes, you want to insert a row if it’s missing but update it if it’s already there. We had asked Microsoft for an UPSERT statement as that’s what it’s called in other databases.

What we got in SQL Server 2008 instead was a MERGE statement. It’s more flexible than an UPSERT statement.

In the example above, I’ve said I want to merge into the dbo.CinemaGroups table. Note that this statement also has an optional INTO word, just like INSERT does. That’s then considered the target table. Only one table can be modified in a single query, including with a MERGE query.

Source of data to merge

The USING clause then says where the new or updated data is coming from and it says how this data is joined to the existing data. In the example, there is a table called dbo.NewGroups but this could be just a VALUES clause used as a row constructor, or it could be a derived table using a sub-select, or it could be a reference to a CTE.

I’ve then said that the CinemaGroupID is how the join happens.

Actions to take

After I’ve identified where the data is coming from, and how it’s joined, I then need to say what I want to do if it’s matched or not.

In the example shown, I’ve said I want to update the CinemaGroupName if a row for the same CinemaGroupID already exists. If it’s not there, I want to insert a new row.

One thing that’s a little odd about the syntax for this,  is that the INSERT and UPDATE clauses don’t have a table name. That’s because only the target table can be modified anyway.

There are more complex options that the MERGE statement can implement but I wanted to make you aware of the statement and how the most common use case is implemented.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-04-09