SQL MERGE How to Insert non duplicates and return duplicates?

Transact-SQL
1

Hello,

In my Blazor Server application, I am inserting a list of data into a SQL database with EF Core 6. There are tens of thousands of data in the database and the list to be compared might be 5000 records. I wonder if I can only insert the non-duplicate values based on 2 columns on the database and return the duplicates? I searched and came up with MERGE but I am not sure if I can do it with it.

By the way, the example below works on 2 tables. In my case, the source will be a list.

MERGE TargetProducts AS Target
USING SourceProducts	AS Source
ON Source.ProductID = Target.ProductID
    
-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price)
    
-- For Returning Duplicates
WHEN MATCHED THEN 
    SELECT ProductID,ProductName, Price
    FROM SourceProducts
2

Don't use merge in this case, it's better to split it into an INSERT and UPDATE statement. You can use SELECT DISTINCT to get unique records or use a GROUP BY.

MERGE (Transact-SQL) - SQL Server | Microsoft Learn

I want to mention this in the above document:

Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it doesn't exist, or updating a row if it matches. When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements. For example:

SQLCopy

INSERT tbl_A (col, col2)  
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);
3

Thank you, let me give you some details.

Excel is the data source I am trying to insert into the database. In Excel, the data is unique but there might have already been inserted into the database before. I am checking the Excel columns Serial and Pin. If separately or both do NOT in the database, this means unique and should be inserted.

USE [GameAPI]
GO
/****** Object:  Table [dbo].[GameBankPins]    Script Date: 22.06.2023 14:10:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GameBankPins](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[GameBankID] [int] NOT NULL,
	[expiryDate] [datetime] NULL,
	[Serial] [nvarchar](max) NULL,
	[Pin] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GameBanks]    Script Date: 22.06.2023 14:10:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GameBanks](
	[GameBankID] [int] IDENTITY(1,1) NOT NULL,
	[referenceId] [uniqueidentifier] NOT NULL,
	[productCode] [nvarchar](max) NULL,
	[quantity] [int] NOT NULL,
	[version] [nvarchar](max) NULL,
	[requestDateTime] [datetime] NULL,
	[customerID] [int] NULL,
	[password] [nvarchar](max) NULL,
	[responseDateTime] [datetime] NULL,
	[initiationResultCode] [nvarchar](max) NULL,
	[companyToken] [nvarchar](max) NULL,
	[used] [int] NOT NULL,
	[productDescription] [nvarchar](max) NULL,
	[currency] [nvarchar](max) NULL,
	[unitPrice] [float] NOT NULL,
	[totalPrice] [float] NOT NULL,
	[ApplicationCode] [nvarchar](max) NULL,
	[estimateUnitPrice] [float] NOT NULL,
	[validatedToken] [nvarchar](max) NULL,
	[signature] [nvarchar](max) NULL,
	[status] [int] NOT NULL,
	[clientTrxRef] [varchar](25) NULL,
 CONSTRAINT [PK_dbo.GameBanks] PRIMARY KEY CLUSTERED 
(
	[GameBankID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[GameBankPins] ON 
GO
INSERT [dbo].[GameBankPins] ([Id], [GameBankID], [expiryDate], [Serial], [Pin]) VALUES (21215, 21217, CAST(N'2022-11-29T00:00:00.000' AS DateTime), N'167722', N'9APV-7E43-E7AV-DJN9')
GO
INSERT [dbo].[GameBankPins] ([Id], [GameBankID], [expiryDate], [Serial], [Pin]) VALUES (21216, 21218, CAST(N'2022-11-29T00:00:00.000' AS DateTime), N'871917', N'K7YT-9P8A-93T3-7KJP')
GO
INSERT [dbo].[GameBankPins] ([Id], [GameBankID], [expiryDate], [Serial], [Pin]) VALUES (21217, 21219, CAST(N'2022-11-29T00:00:00.000' AS DateTime), N'493612', N'VERE-NTP9-EJP8-CE9C')
GO
INSERT [dbo].[GameBankPins] ([Id], [GameBankID], [expiryDate], [Serial], [Pin]) VALUES (21218, 21220, CAST(N'2022-11-29T00:00:00.000' AS DateTime), N'447573', N'8DD8-F3JV-VAKK-FERC')
GO
INSERT [dbo].[GameBankPins] ([Id], [GameBankID], [expiryDate], [Serial], [Pin]) VALUES (21219, 21221, CAST(N'2022-11-29T00:00:00.000' AS DateTime), N'640161', N'94DE-PTK3-V7DF-0011')
GO

SET IDENTITY_INSERT [dbo].[GameBankPins] OFF
GO
SET IDENTITY_INSERT [dbo].[GameBanks] ON 
GO
INSERT [dbo].[GameBanks] ([GameBankID], [referenceId], [productCode], [quantity], [version], [requestDateTime], [customerID], [password], [responseDateTime], [initiationResultCode], [companyToken], [used], [productDescription], [currency], [unitPrice], [totalPrice], [ApplicationCode], [estimateUnitPrice], [validatedToken], [signature], [status], [clientTrxRef]) VALUES (21217, N'6249cb71-348f-4927-b8fc-9eab93e22c8f', N'OYNPLS000001', 1, N'V1', CAST(N'2021-12-28T08:40:55.833' AS DateTime), NULL, NULL, CAST(N'2021-12-28T08:40:55.833' AS DateTime), NULL, NULL, 0, N'Free Fire 100 + 50 Elmas', N'TRY', 10.25, 10.25, NULL, 0, NULL, N'Cenk', 1, N'0000001')
GO
INSERT [dbo].[GameBanks] ([GameBankID], [referenceId], [productCode], [quantity], [version], [requestDateTime], [customerID], [password], [responseDateTime], [initiationResultCode], [companyToken], [used], [productDescription], [currency], [unitPrice], [totalPrice], [ApplicationCode], [estimateUnitPrice], [validatedToken], [signature], [status], [clientTrxRef]) VALUES (21218, N'dc180d55-bca7-41f3-8449-dc56fce7bc82', N'OYNPLS000002', 1, N'V1', CAST(N'2021-11-28T16:19:59.673' AS DateTime), NULL, NULL, CAST(N'2021-11-28T16:19:59.673' AS DateTime), NULL, NULL, 0, N'Free Fire 210 + 105 Elmas', N'TRY', 20.5, 20.5, NULL, 0, NULL, N'Cenk', 1, NULL)
GO
INSERT [dbo].[GameBanks] ([GameBankID], [referenceId], [productCode], [quantity], [version], [requestDateTime], [customerID], [password], [responseDateTime], [initiationResultCode], [companyToken], [used], [productDescription], [currency], [unitPrice], [totalPrice], [ApplicationCode], [estimateUnitPrice], [validatedToken], [signature], [status], [clientTrxRef]) VALUES (21219, N'd37c6c46-41a0-4d10-95fd-095b97737945', N'OYNPLS000003', 1, N'V1', CAST(N'2021-11-28T16:25:36.997' AS DateTime), NULL, NULL, CAST(N'2021-11-28T16:25:36.997' AS DateTime), NULL, NULL, 0, N'Free Fire 530 + 265 Elmas', N'TRY', 51.25, 51.25, NULL, 0, NULL, N'Cenk', 1, NULL)
GO
INSERT [dbo].[GameBanks] ([GameBankID], [referenceId], [productCode], [quantity], [version], [requestDateTime], [customerID], [password], [responseDateTime], [initiationResultCode], [companyToken], [used], [productDescription], [currency], [unitPrice], [totalPrice], [ApplicationCode], [estimateUnitPrice], [validatedToken], [signature], [status], [clientTrxRef]) VALUES (21220, N'0e85fa18-ce64-4289-900f-6c5935050ee7', N'OYNPLS000004', 1, N'V1', CAST(N'2021-11-28T16:25:35.793' AS DateTime), NULL, NULL, CAST(N'2021-11-28T16:25:35.793' AS DateTime), NULL, NULL, 0, N'Free Fire 1080 + 540 Elmas', N'TRY', 102.5, 102.5, NULL, 0, NULL, N'Cenk', 1, NULL)
GO
INSERT [dbo].[GameBanks] ([GameBankID], [referenceId], [productCode], [quantity], [version], [requestDateTime], [customerID], [password], [responseDateTime], [initiationResultCode], [companyToken], [used], [productDescription], [currency], [unitPrice], [totalPrice], [ApplicationCode], [estimateUnitPrice], [validatedToken], [signature], [status], [clientTrxRef]) VALUES (21221, N'5b28823f-41bb-4ddc-990b-31810deb7b64', N'OYNPLS000005', 1, N'V1', CAST(N'2021-11-28T17:32:56.523' AS DateTime), NULL, NULL, CAST(N'2021-11-28T17:32:56.523' AS DateTime), NULL, NULL, 0, N'Free Fire 2200 + 1100 Elmas', N'TRY', 205, 205, NULL, 0, NULL, N'Cenk', 1, NULL)
GO

SET IDENTITY_INSERT [dbo].[GameBanks] OFF
GO
/****** Object:  Index [PK_dbo.GameBankPins]    Script Date: 22.06.2023 14:10:48 ******/
ALTER TABLE [dbo].[GameBankPins] ADD  CONSTRAINT [PK_dbo.GameBankPins] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
4

I don't understand your question. Serial and Pin are in the GameBankPins but not in the other table. How can I check if they are unique?