CODEWITHSUNDEEP
sqlsql-server
ScripterPS1230083
ScripterPS1230083

Reputation: 19

SQL INSERT only unique values in table without errors

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.

Upvotes: 0

Views: 859

Answers (3)

ScripterPS1230083
ScripterPS1230083

Reputation: 19

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.

Upvotes: 0

Roshan N
Roshan N

Reputation: 1053

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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);

Upvotes: 0

Related Questions