Insert only when that records doesn't exists in table

Question

Hi,

I am using Sql server 2005.

I have to give SQL insert script to my client. But before it insert into table. I want to check if that records already exists in table, it should not insert.

Below are the insert query

INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder]) VALUES (UsageLevel,High,1)
INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder]) VALUES (UsageLevel,Medium,2)
INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder]) VALUES (UsageLevel,Low,3)

The Table tblStatus has four fields id,type,name,displayorder. In which ID is autogenerated.

Please help!

Thanks.

Best Regards, MS

Answer 1

Easiest way is the not exists statement, something like the following should work for you

note Unless I'm misunderstanding your schema, I think you're missing some quotes around the Name and Type columns, I've included them below

if not exists (
    SELECT NULL as test FROM dbo.tblStatus 
    WHERE [Type] = 'UsageLevel' AND [Name] = 'High'
)
BEGIN
    INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder]) 
    VALUES ('UsageLevel','High',1)
END

if not exists (
    SELECT NULL as test FROM dbo.tblStatus 
    WHERE [Type] = 'UsageLevel' AND [Name] = 'Medium'
)
BEGIN
    INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder])
    VALUES ('UsageLevel','Medium',2)
END

if not exists (
    SELECT NULL as test FROM dbo.tblStatus
    WHERE [Type] = 'UsageLevel' AND [Name] = 'Low'
)
BEGIN
    INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder])
    VALUES ('UsageLevel','Low',3)
END

Answer 2

Create unique index based on the items that you dont want to insert, you can have multi column unique index that should help. And if you are looking this script as temporary and one time use only then you can create index and delete index later on. This index will prevent the re entry of the existing items.

Answer 3

Here is another solution:

http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/