Insert record only if record does not already exist in table

Question

I'm wondering if there is a way to insert a record into a table only if the table does not already contain that record?

Is there a query that will do this, or will I need a stored procedure?

Answer 1

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 

Answer 2

IF NOT EXISTS      (SELECT {Columns}      FROM {Table}      WHERE {Column1 = SomeValue AND Column2 = SomeOtherVale AND ...})  INSERT INTO {Table} {Values}