You don’t say what version of SQL Server. If SQL Server 2008 you can use MERGE
NB: It is usual to use Merge for an Upsert which is what I originally thought the question was asking but it is valid without the WHEN MATCHED
clause and just with a WHEN NOT MATCHED
clause so does work for this case also. Example Usage.
CREATE TABLE #A(
[id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[C] [varchar](200) NOT NULL)
MERGE #A AS target
USING (SELECT 3, 'C') AS source (id, C)
ON (target.id = source.id)
/*Uncomment for Upsert Semantics
WHEN MATCHED THEN
UPDATE SET C = source.C */
WHEN NOT MATCHED THEN
INSERT (id, C)
VALUES (source.id, source.C);
In terms of execution costs the two look roughly equal when an Insert is to be done…
Link to plan images for first run
but on the second run when there is no insert to be done Matthew’s answer looks lower cost. I’m not sure if there is a way of improving this.
Link to plan images for second run
Test Script
select *
into #testtable
from master.dbo.spt_values
CREATE UNIQUE CLUSTERED INDEX [ix] ON #testtable([type] ASC,[number] ASC,[name] ASC)
declare @name nvarchar(35)= 'zzz'
declare @number int = 50
declare @type nchar(3) = 'A'
declare @low int
declare @high int
declare @status int = 0;
MERGE #testtable AS target
USING (SELECT @name, @number, @type, @low, @high, @status) AS source (name, number, [type], low, high, [status])
ON (target.[type] = source.[type] AND target.[number] = source.[number] and target.[name] = source.[name] )
WHEN NOT MATCHED THEN
INSERT (name, number, [type], low, high, [status])
VALUES (source.name, source.number, source.[type], source.low, source.high, source.[status]);
set @name="yyy"
IF NOT EXISTS
(SELECT *
FROM #testtable
WHERE [type] = @type AND [number] = @number and name = @name)
BEGIN
INSERT INTO #testtable
(name, number, [type], low, high, [status])
VALUES (@name, @number, @type, @low, @high, @status);
END