Tutorial :Insert only when that records doesn't exists in table



Question:

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


Solution: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  


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »