SQL INSERT only unique values in table without errors

Question

In my SQL script, I have about 200 plus entries with INSERT statement in them. My script looks like this:

SET IDENTITY_INSERT [BH].[LanguageFiles]
GO

INSERT INTO [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value]) 
VALUES (44, N'HomePage', NULL, 8, N'Welcome to the app')
GO

INSERT INTO [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value]) 
VALUES (45, N'LogIn', NULL, 8, N'Enter your assigned credentials')
GO

INSERT INTO [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value]) 
VALUES (46, N'LogOut', NULL, 8, N'Come back soon')
GO

INSERT INTO [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value]) 
VALUES (47, N'RedirectAbout', NULL, 8, N'About Us')
GO

INSERT INTO [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value]) 
VALUES (48, N'Contact', NULL, 8, N'Consultation')
GO

SET IDENTITY_INSERT [BH].[LanguageFiles] OFF
GO

There are more than 200 INSERT INTO statements here. If it was shorter I could do something like this:

IF NOT EXISTS ( SELECT * FROM [BH].[LanguageFiles] WHERE Id = 46)
    INSERT INTO [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value]) 
    VALUES (46, N'LogOut', NULL, 8, N'Come back soon')

However, that is not feasible in this situation with so many entries. What could I do instead to fix this?

At the moment, when I run this script I get the following error:

Exception Message: Violation of PRIMARY KEY constraint 'Tbl_LanguageFiles'. Cannot insert duplicate key in object '[BH].[LanguageFiles]'. The duplicate key value is (46)

SOLUTION

Thank you for the answers everyone. One way of going about it was using IF NOT EXISTS however, that was looking to be very lengthy as I would have to write it for all 250 entries. I ended up copying this in Excel and using CONCAT to insert the IF NOT EXISTS statement before with its respective condition.

Why isn't it feasible to do the existence check on each statement? You can write a script to generate that check statement for each of your inserts. — shree.pat18, Apr 28 '22 at 06:32
How would I go about generating that script? That bit's new to me if you could help me with that I'd really appreciate it — ScripterPS1230083, Apr 28 '22 at 06:41
You can insert all the data into a temp table and write not in while inserting into Actual table — Roshan Nuvvula, Apr 28 '22 at 07:09
Your error above is happening because there is already an existing record with ID 46 in target table? Or is it because multiple insert statements have that ID? — shree.pat18, Apr 28 '22 at 07:13
@shree.pat18 It's happening because a record with the same Id already exists. — ScripterPS1230083, Apr 28 '22 at 09:04

score 1 · Answer 1 · answered Apr 28 '22 at 07:20

Can you try this

CREATE TEMPORARY TABLE [BH].[LanguageFiles1]
AS 
SELECT * FROM [BH].[LanguageFiles]
WHERE 1=2;

GO
INSERT [BH].[LanguageFiles1] ([Id], [Key], [Secret], [LangId], [Value]) VALUES(44,N'HomePage',NULL,8,N'Welcome to the app')
GO
INSERT [BH].[LanguageFiles1] ([Id], [Key], [Secret], [LangId], [Value]) VALUES(45,N'LogIn',NULL,8,N'Enter your assigned credentials')
GO
INSERT [BH].[LanguageFiles1] ([Id], [Key], [Secret], [LangId], [Value]) VALUES(46,N'LogOut',NULL,8,N'Come back soon')
GO
INSERT [BH].[LanguageFiles1] ([Id], [Key], [Secret], [LangId], [Value]) VALUES(47,N'RedirectAbout',NULL,8,N'About Us')
GO
INSERT [BH].[LanguageFiles1] ([Id], [Key], [Secret], [LangId], [Value]) VALUES(48,N'Contact',NULL,8,N'Consultation')
GO
SET IDENTITY_INSERT [BH].[LanguageFiles]
GO
INSERT [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value])
SELECT [Id], [Key], [Secret], [LangId], [Value] 
FROM LanguageFiles1
WHERE ID NOT IN (SELECT ID FROM [BH].[LanguageFiles])
GO
SET IDENTITY_INSERT [BH].[LanguageFiles] OFF

Temporary table syntax is not correct. – Yogesh Sharma Apr 28 '22 at 09:02 — Yogesh Sharma, Apr 28 '22 at 09:02

score 0 · Answer 2 · answered Apr 28 '22 at 06:38

0

You may use an INSERT INTO ... SELECT along with an exists clause, e.g.

INSERT [BH].[LanguageFiles] ([Id], [Key], [Secret], [LangId], [Value])
SELECT 44, N'HomePage', NULL, 8, N'Welcome to the app'
WHERE NOT EXISTS (SELECT 1 FROM [BH].[LanguageFiles] WHERE Id = 44);

answered Apr 28 '22 at 06:38

Tim Biegeleisen

502,043
27
286
360

This is giving me the same error sadly. – ScripterPS1230083 Apr 28 '22 at 06:57

score 0 · Answer 3 · answered Apr 28 '22 at 09:08

SOLUTION

Thank you for the answers, everyone. One way of going about it was using IF NOT EXISTS however, that was looking to be very lengthy as I would have to write it for all 250 entries. I ended up copying this in Excel and using CONCACT to insert the `IF NOT EXISTS statement before with its respective condition.

SQL INSERT only unique values in table without errors

3 Answers3