Category: Blog

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

This is a fairly simple process, but I bet more than a few people don’t know how to do it. I had to double check some syntax the other day, and I thought this is a perfect SQLNewBlogger post, based on something I had to (re)learn.

If I have a table, there are existing columns and I want to add a constraint to one, I need to alter it. To do that, I’ll use the ALTER TABLE x ADD CONSTRAINT syntax.

One of the common use cases is to add a default date to a date column. For example, I have a Blogs table and want to ensure the CreatedDate column is always populated. I’d do that with

ALTER TABLE dbo.Blogs
ADD CONSTRAINT df_SysUTCDate DEFAULT SYSDATETIME() FOR createdate;

In this case, I use ADD CONSTRAINT and then name the constraint. Using specific names is always good since this means I can be sure that I have matches between development, QA, and production.

I then use the DEFAULT keyword and follow this with a function name. I then use the FOR and the column name. This means I’ve added a default constraint to the CreateDate column and if no value is included in an insert statement, sysdatetime() used.

This is a basic idea, but one that few developers think about or include in their design. Learn to use defaults and add them when you start building or adding columns.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was playing with some data, loading it into staging tables and then moving it to a real table. I typically have done this with T-SQL, writing efficient upsert code that works well. However, I haven’t used Merge in a long time and thought I should practice a bit with the structure.

Note: Merge isn’t that efficient and most experts do not recommend it (Aaron Bertrand, Dwain Camps). If you decide to use Merge, make sure you are aware of performance implications with your system. It should be fine with smaller sets, but be aware of potential issues if your data scale grows.

There’s a nice Simple Talk article on Merge that helps you understand how this structure works. There are lots of tips and tricks with merge, but the basic idea is that I can decide to merge data from one table into another and handle the various cases of rows that exist or don’t exist, and what to do. This is the classic upsert, where we insert new rows and update existing ones.

A Quick Scenario

I was actually playing with some SQL Saturday data, so let’s use that and set up a few tables. We’ll set up an Event table and an EventStaging table with some data.

CREATE TABLE Event
(EventID INT PRIMARY KEY CLUSTERED
, EventName VARCHAR(200)
, City VARCHAR(100)
, EventDate DATE
)
GO

CREATE TABLE EventStaging
(EventID INT PRIMARY KEY
, EventName VARCHAR(200)
, City VARCHAR(100)
, EventDate DATE
)
GO

INSERT Event
  VALUES 
   (1  , 'SQLSaturday #1 - Orlando 2007', 'Orlando', '2007-11-10')
, (4  , 'SQLSaturday #4 Tweener(Sun) - Orlando 2008', 'Orlando', '2008-06-05')
, (2  , 'SQLSaturday #2 - Tampa 2008', 'Tampa', '2008-02-15')
, (3  , 'SQLSaturday #3 - Jacksonville 2008', 'Jacksonville', '2008-05-03')

insert dbo.EventStaging
  values 
   (4  , 'SQLSaturday #4 - Orlando 2008', 'Orlando', '2008-06-07')
, (5  , 'SQLSaturday #5 - Olympia 2008', 'Olympia', '2008-10-11')
, (6  , 'SQLSaturday #6 - Cleveland 2008', 'Cleveland', '2009-02-01')
, (7  , 'SQLSaturday #7 - Birmingham 2009', 'Birmingham', '2009-05-30')

The data is loaded into EventStaging and then needs to move to Event for the application. If you examinet the data, you’ll see that the events with ID =4 is in both tables with different data. Events 5, 6, 7 are only in the staging table and need to be moved.

We can see the data here:

To move this data, let’s start with the merge header

MERGE dbo.Event ev
  USING dbo.EventStaging es
  ON ev.EventID = es.EventID

This opening is looking to merge data into the Event table using the EventStaging table as a data source. The join is included in the ON statement and follows the rules like any other join clause.

The next part of the statement is similar to a CASE statement, with a series of WHEN MATCHED or WHEN NOT MATCHED statements with THEN clauses that determine what happened.

MERGE dbo.Event ev
  USING dbo.EventStaging es
  ON ev.EventID = es.EventID
  WHEN MATCHED 
   THEN UPDATE 
    SET ev.EventName = es.EventName
      , ev.City = es.City
      , ev.EventDate = es.EventDate
  WHEN NOT MATCHED
   THEN INSERT (EventID, EventName, City, EventDate)
         VALUES (es.EventID, es.EventName, es.City, es.EventDate);

The two statements I have listed handle the update and insert. The first says that when we match a row, meaning there is a row in EventStaging that matches Event on the EventID, we will update the Event table (that’s the MERGE target). In this case, the rows 5, 6, 7 will fall into this case.

The WHEN NOT MATCHED is when there is a row in EventStaging that isn’t in Event, we insert the data. Note again, we don’t need to specify the table name in the INSERT.

When we run this command, the four rows in EventStaging are processed, with 3 inserts and 1 update. After running this, we can see the results here:

The name of the Orlando second event (#4) has changed, as has the date.

This is a quick look at Merge, and a handy command that you should consider using with smaller sets of data.