SQL Script – Insert into table only if row does not exist

There is always a time when you would like to insert data into a table only if row does not exist

Do you need those re-runable script so you can confidently run it multiple time without worrying about errors or duplicates?

Here is an easy way to insert data into a table using a Left Join on itself with unique key matching. SQL Script – Insert into table only if row does not exist

Here is the script

declare @tblSample table (Id int identity(1, 1)
						, key1 int
						, key2 int
						, val varchar(10))

insert into @tblSample (Key1, key2, val)
	select 1, 10, 'test1110'
	union
	select 2, 20, 'test220'
	union
	select 3, 30, 'test330'
	union 
	select 4, 40, 'test440'


select * from @tblSample

-- prepare to insert into @tblsample only values that do not exist
insert into @tblSample ([key1]
							, [key2]
							, [val])
	select V.[Key1], V.[Key2], V.[val]
		from
		(
			select [key1] = 4, [key2] = 40, [val] = 'test440'
			union
			select [key1] = 5, [key2] = 50, [val] = 'test550'

		) V

		left join @tblSample t on t.[Key1] = V.[key1] and t.[key2] = V.[key2]
	where t.[Id] is null  -- get all rows that do no already have the two keys
-- NOTE THAT ROW WITH KEY1 = 4 AND KEY2 = 440 IS NOT INSERTED SECOND TIME AS IT ALREADY EXISTS 

select * from @tblSample

Happy coding!