I work with SQL Server and plan to regularly share many of the cool things I get to do and how I go about implementing them and for my first post I’m going to demonstrate how to update existing rows in a table from date from a flat file source using both an UPDATE and MERGE statements.

Scope

Create a table for demonstration, export data from the table into a text file, and then using both an UPDATE and a MERGE statement, modify the data in the database from the data in the text file.

Tasks

1. Create the database structure that will be the target of the demonstration.
2. Export data from the database into a text file.
3. Modify the table data so there is something to change from the text file.
4. Use an UPDATE statement to update the table data.
5. Use a MERGE statement to update the table data.

Step 1 - Create database and tables

The first thing we need to do is create the database that we’re going to use through the demonstration. I’ve created one for this blog that I will be using as the basis of all my weblogs, if you want to download the database you can find it here, otherwise if you’re learning I would encourage you to follow the steps I’ve taken.

CREATE DATABASE Fiftynine

USE Fiftynine
GO

CREATE TABLE dbo.Category(
    ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Name nvarchar(50) NOT NULL
    )

CREATE TABLE dbo.Entry(
    ID int IDENTITY(1,1) NOT NULL,
    Title nvarchar(500) NOT NULL,
    Body nvarchar(max) NOT NULL,
    EntryDate datetime NOT NULL,
    Category int NOT NULL
    )

ALTER TABLE dbo.Entry  WITH CHECK ADD  CONSTRAINT FK_Entry_Category FOREIGN KEY(Category)
REFERENCES dbo.Category (ID)
GO

ALTER TABLE dbo.Entry CHECK CONSTRAINT FK_Entry_Category
GO

INSERT Category
VALUES ('Update'),('Merge')

INSERT Entry
VALUES ('How to perform an update','This is how to perform an update',getdate(),1),
       ('How to perform an update','This is how to perform a merge',getdate(),2)

Step 2 - Export data

We’re going to use the bcp Utility to export the data out into text so if you don’t already have xp_cmdshell enabled do it now.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Now we’re ready to export the data into a text.

DECLARE @bcpcmd varchar(255), @outputfile varchar(255), @filepath varchar(255)
        --1.
    SET @bcpcmd = 'bcp "SELECT * FROM Fiftynine.dbo.Entry" queryout '
    --2.         
    SET @outputfile = 'EntryOutput-'+convert(varchar,getdate(),105) + '.txt"'
        --3.
    SET @filepath = 'C:\Temp\'
    --4.         
    SET @bcpcmd = @bcpcmd + '"' + @filepath + @outputfile + ' -c -T -S'+@@servername+''
    --5.         
    EXEC xp_cmdshell @bcpcmd, no_output

What we’re doing here is:

• Set the @bcpcmd variable to be SELECT query we want to output and using the queryout switch copies the result set from the query.
• Sets the filename, in this case I’ve named it using the table name followed by the string ‘Output’ and the date using the GETDATE() function.
• Set the location where the text file is going to be stored, in my case C:\Temp. If the directory doesn’t already exist the bcp operation will fail so make sure you have a C:\Temp location.
• Concatenate the three variables to create the full @bcpcmd that xp_cmdshell is going to execute.
• Execute xp_cmdshell, passing the @bcpcmd variable and the no_output switch stops the messages from being displayed in SQL Server Management Studio.

Step 3 - Modifying the text data

You’ll notice that I made an error in the INSERT that we performed just after creating the tables. The title for boh entries into dbo.Entry are named the same. Let’s correct this in the text file

Step 4 - Use an UPDATE statement to correct the mistake

CREATE TABLE #InsertTemp (ID int, Title nvarchar(500), Body nvarchar(max), Entrydate datetime, Category int);

BULK INSERT #InsertTemp FROM 'C:\temp\EntryOutput-10-09-2016.txt'WITH(ROWTERMINATOR ='\n');

UPDATE Entry SET Entry.Title = t.Title FROM Entry ejoin #InsertTemp t on e.ID = t.ID WHERE e.ID = 2;

DROP TABLE #InsertTemp;

Step 5 - Use a MERGE statement to correct the mistake

CREATE TABLE #InsertTemp
(ID int, Title nvarchar(500), Body nvarchar(max), Entrydate datetime, Category int);

BULK INSERT #InsertTemp
FROM 'C:\temp\EntryOutput-10-09-2016.txt'WITH(ROWTERMINATOR ='\n');

MERGE INTO Entry AS TARGET
USING #InsertTemp AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED AND TARGET.Title <> SOURCE.Title THEN
UPDATE SET TARGET.Title  = SOURCE.Title
WHEN NOT MATCHED BY TARGET THEN
INSERT (Title, Body, EntryDate, Category)
VALUES (SOURCE.Title, SOURCE.Body, SOURCE.EntryDate, SOURCE.Category);

DROP TABLE #InsertTemp;

Some stuff about merge.

View the full script here, or download it directly from here. (Both hosted by Github).

Back to posts