How to insert multiple literals explicitly into a table, only if they don’t already exist

It might seem silly, but recently I needed to insert some values into a new table I added to a system, but only if those values didn’t already exist, and I did not know how. (Yes, it’s a new table and is thus empty. But this technique should work when adding other values into it in future.) I know how to do that when inserting a selection from another table, but in this case I wanted to insert literals, and all my Googling came up empty…

So even though this is really quite an easy task, I figured writing it here might help somebody from going to the few minutes of trouble I had to. (And more importantly, forgetful me can refer to this post when I forget how I did it.)

To illustrate what I mean, let’s start with a simple table, which will hold the names of some super heroes. (I can’t give the details of the real table I created if I value my employment.) Because I’m lazy, I create tables in the designer in SQL Server Management Studio, and then script them afterwards. The creation script for such a table could be as follows:

CREATE TABLE [dbo].[JusticeLeague](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](10) NOT NULL,
    [Description] [varchar](100) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_JusticeLeague] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I usually insert multiple literals like so:

DECLARE @Today datetime = getdate();

INSERT INTO JusticeLeague (Code, Description, DateCreated) values
('SUP', 'Superman', @Today),
('BAT', 'Batman', @Today),
('WON', 'Wonder Woman', @Today),
('FLASH', 'The Flash', @Today),
('AQUM', 'Aquaman', @Today)

But that doesn’t prevent duplicates. What I want my script to do is:

  • Insert the rows only if they aren’t already in the table.
  • If run more than once, it should only insert the first time. When run again, it should do nothing.
  • If new rows are added to the script, when rerun it should only insert those new rows.

Since my searches came up empty (actually I got nothing but irrelevant results), I came up with a way… All this does is:

  1. Select the values as if they are a table, by selecting all rows’ explicit values and doing a union.
  2. Then add a filter using the EXISTS keyword.

It works quite well. So my solution was this:

DECLARE @Today datetime = getdate();

INSERT INTO JusticeLeague (Code, Description, DateCreated)
select t.* from (
select 'SUP' Code, 'Superman' Description, @Today DateCreated union all
select 'BAT', 'Batman', @Today union all
select 'WON', 'Wonder Woman', @Today union all
select 'FLASH', 'The Flash', @Today union all
select 'AQUM', 'Aquaman', @Today) t
where not exists(select 1 from JusticeLeague i where i.Code = t.Code)

This script can be rerun, and when adding new values, which will definitely happen in the real table that I based this contrived example on, the script can safely be rerun and only the new rows will be inserted.

For example, I can safely change the script as below, and only one new hero, Green Lantern, will be added:

DECLARE @Today datetime = getdate();

INSERT INTO JusticeLeague (Code, Description, DateCreated)
select t.* from (
select 'SUP' Code, 'Superman' Description, @Today DateCreated union all
select 'BAT', 'Batman', @Today union all
select 'WON', 'Wonder Woman', @Today union all
select 'FLASH', 'The Flash', @Today union all
select 'GRE', 'Green Lantern', @Today union all
select 'AQUM', 'Aquaman', @Today) t
where not exists(select 1 from JusticeLeague i where i.Code = t.Code)

Note that I’m using UNION ALL. This would actually cause duplicates if the script contained duplicate values, but performs better than UNION, which scans for duplicates. This is fine for me, because when building the script, I take care not to include duplicate literals.

Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s