CODEWITHSUNDEEP
sql-server
Manoj Singh
Manoj Singh

Reputation: 7707

Insert only when that records doesn't exists in table

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

Upvotes: 0

Views: 574

Answers (2)

Dan F
Dan F

Reputation: 12052

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

Upvotes: 1

Akash Kava
Akash Kava

Reputation: 39916

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.

Upvotes: 0

Related Questions