How would you compare an excel with data in the database table?

Cenk · January 12, 2023, 5:58am

Hello folks,

I am working on a Blazor Server application. I would like to upload an excel file, consisting of a couple of columns. ( ReferenceId -guid, Order Date- datetime, Product Code- string, and Product Description -string) This excel file roughly has 200000 rows of data.
I am gonna use EPPlus non-commercial package. I will compare the referenceId's from the excel with the database but there might be duplicate referenceId's. So I am somehow grouping those referenceId's in excel and then iterating it comparing them with the database records.
One last requirement is to write down both matching and nonmatching referenceId's in an excel file. I think when comparing the referenceId's adding those values into two separate lists (matching, non-matching) and then writing those records inside of those lists in excel with EPPlus will solve my problem.

Can I have your comments about this solution? Can it be improved? Is it more efficient to do the comparison on the database side?

Thank you.

Cenk · January 13, 2023, 5:01am

Any recommendations?

RogierPronk · January 13, 2023, 12:20pm

It will always be more efficient to compare it on the database side but your solution is ok. If you are not a database expert with for example stored procedures, indexes etc it would be the way to go.

The only thing you should care about is the performance. Don't create a solution where you compare them row by row but do it in parts for example 1000 rows to prevent locking other users. I don't know if it's a heavy used application but that whould be my concern. Maybe you can even try to parameter the size so you can analyse the performance with different settings.

And keep in mind that if this is working you can expect to upload other Excel-files as well so be prepered for that.

Cenk · January 13, 2023, 12:49pm

If I upload excel records into the database with bulk insert, how can I compare 2 tables and select matching and non-matching records?

RogierPronk · January 14, 2023, 8:17am

There are several ways to compare 2 tables.

You can create a stored procedure and use inner join to update, left join to insert. You can use the output to give feedback. You can find a lot on youtube.

TSQL – Sync tables with easy Stored Procedure - YouTube

Cenk · January 17, 2023, 1:39pm

I watched the video but unfortunately, I am not good at SQL. I hope you experts can guide me.

Here is GameConfirmResponse table:

CREATE TABLE [dbo].[GameConfirmResponses](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[referenceId] [uniqueidentifier] NOT NULL,
	[version] [nvarchar](max) NULL,
	[signature] [nvarchar](max) NULL,
	[paymentID] [nvarchar](max) NULL,
	[productCode] [nvarchar](max) NULL,
	[quantity] [int] NOT NULL,
	[deliveredQuantity] [nvarchar](max) NULL,
	[productDescription] [nvarchar](max) NULL,
	[totalPayablePrice] [float] NOT NULL,
	[currency] [nvarchar](max) NULL,
	[ApplicationCode] [nvarchar](max) NULL,
	[unitPrice] [float] NOT NULL,
	[totalPrice] [float] NOT NULL,
	[purchaseStatusCode] [nvarchar](max) NULL,
	[purchaseStatusDate] [datetime] NULL,
	[requestDateTime] [datetime] NULL,
	[merchantProductCode] [nvarchar](max) NULL,
	[used] [int] NOT NULL,
	[status] [int] NOT NULL,
	[service] [nvarchar](max) NULL,
	[clientTrxRef] [varchar](25) NULL,
 CONSTRAINT [PK_dbo.GameConfirmResponses] PRIMARY KEY CLUSTERED 
(
	[Id] 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].[GameConfirmResponses] ON 
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (1, N'8c564293-5575-4fa0-8c38-0631a2e10aef', N'V1', N'030065621f01d635da918281f5bb1f23', N'MDO245211', N'000000001570', 1, N'1', N'10 TL''lik Steam Bakiyesi', 9.4, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 10, 10, N'00', CAST(N'2019-10-07T21:53:55.000' AS DateTime), CAST(N'2019-10-07T21:53:49.690' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2, N'eede1c93-784c-4935-9c4c-52fbb75180c6', N'V1', N'ac30730994598a1c0e39114bcbdad945', N'MDO245212', N'000000001571', 1, N'1', N'25 TL''lik Steam Bakiyesi', 23.5, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 25, 25, N'00', CAST(N'2019-10-07T21:54:10.000' AS DateTime), CAST(N'2019-10-07T21:54:04.087' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (3, N'8ecd4034-2c29-410e-a37b-1b2d06425c38', N'V1', N'e9b0ca1ef13c660c445c960d4dd58447', N'MDO245213', N'000000001572', 1, N'1', N'Bim 400 Riot Points', 10.8, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 12, 12, N'00', CAST(N'2019-10-07T21:54:22.000' AS DateTime), CAST(N'2019-10-07T21:54:16.053' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (4, N'90b20141-bee5-45fe-ae68-351bfb94346b', N'V1', N'db01c2f901e64e1090a72f791c084b3b', N'MDO245214', N'000000001573', 1, N'1', N'Bim 840 Riot Points', 21.6, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 24, 24, N'00', CAST(N'2019-10-07T21:54:29.000' AS DateTime), CAST(N'2019-10-07T21:54:23.830' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (5, N'19781ef9-a6b7-4446-affe-8a725f903e77', N'V1', N'120c93bd53b1d79b0640e075b9fba3cb', N'MDO245215', N'000000001574', 1, N'1', N'25 Hükümdarlık', 5.4, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 6, 6, N'00', CAST(N'2019-10-07T21:54:41.000' AS DateTime), CAST(N'2019-10-07T21:54:34.857' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (6, N'd7542952-1a16-474f-933f-4268de78e2b6', N'V1', N'4d57293b35d58a8f0b5a984eb286f636', N'MDO245216', N'000000001575', 1, N'1', N'50 Hükümdarlık', 10.8, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 12, 12, N'00', CAST(N'2019-10-07T21:54:46.000' AS DateTime), CAST(N'2019-10-07T21:54:40.577' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (7, N'd4610ae4-977c-414d-b9f8-1404dd709c47', N'V1', N'41cb100b3b906b5079ba10d347044220', N'MDO245217', N'000000001582', 1, N'1', N'Bim 10 TL Razer Gold Pin', 9, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 10, 10, N'00', CAST(N'2019-10-07T21:54:56.000' AS DateTime), CAST(N'2019-10-07T21:54:50.087' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (8, N'8e92bb77-216a-4574-ae9d-eda8079eb4d4', N'V1', N'13e4ab5f286a05308057e5be1c27b908', N'MDO245218', N'000000001583', 1, N'1', N'Bim 25 TL Razer Gold Pin', 22.5, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 25, 25, N'00', CAST(N'2019-10-07T21:55:02.000' AS DateTime), CAST(N'2019-10-07T21:54:56.557' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (9, N'c28f966a-d66f-4fb7-9259-a81793067e9d', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-07T22:00:54.607' AS DateTime), CAST(N'2019-10-07T22:00:54.390' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (10, N'8d4b59ec-d4a5-40c3-8d56-2021cf1acdd1', N'V1', NULL, NULL, N'000000001586', 1, NULL, N'Bim 10.000 ZA', 0, N'TRY', NULL, 25, 25, NULL, CAST(N'2019-10-07T22:01:08.333' AS DateTime), CAST(N'2019-10-07T22:01:08.320' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (11, N'4dde284c-233e-42b3-bbdb-3ff3a0ec4990', N'V1', N'02c90a2c10245718570ba77de4974bbb', N'MDO245224', N'000000001570', 1, N'1', N'10 TL''lik Steam Bakiyesi', 9.4, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 10, 10, N'00', CAST(N'2019-10-08T15:16:36.000' AS DateTime), CAST(N'2019-10-08T15:16:27.643' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (12, N'a8c83932-5b60-42f7-b3f1-97b51eab31cc', N'V1', N'cb258c2cfbcd53307d19fe4a4f1471f1', N'MDO245225', N'000000001571', 1, N'1', N'25 TL''lik Steam Bakiyesi', 23.5, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 25, 25, N'00', CAST(N'2019-10-08T15:17:49.000' AS DateTime), CAST(N'2019-10-08T15:17:40.713' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (13, N'c50c287c-399f-4a02-b62c-9094b6647501', N'V1', N'f74777744a4172cfb3e996d7146b3304', N'MDO245226', N'000000001572', 1, N'1', N'Bim 400 Riot Points', 10.8, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 12, 12, N'00', CAST(N'2019-10-08T15:19:45.000' AS DateTime), CAST(N'2019-10-08T15:19:37.463' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (14, N'eb124b64-b6da-48a8-a9cd-91ce7a03985f', N'V1', N'c341bd4c9c36225717d132b69c2781cc', N'MDO245227', N'000000001573', 1, N'1', N'Bim 840 Riot Points', 21.6, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 24, 24, N'00', CAST(N'2019-10-08T15:20:18.000' AS DateTime), CAST(N'2019-10-08T15:20:10.030' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (15, N'dbc29bc6-b733-4e0d-a9d4-5ab3436e3b71', N'V1', N'db08b2ac2a824255b01a12ffbacf5104', N'MDO245228', N'000000001574', 1, N'1', N'25 Hükümdarlık', 5.4, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 6, 6, N'00', CAST(N'2019-10-08T15:22:22.000' AS DateTime), CAST(N'2019-10-08T15:22:14.030' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (16, N'2c960688-7a9b-4ae7-a0ad-afffda530c18', N'V1', N'6f5b3122cbc5d9da6d7a420ed08dc8ae', N'MDO245229', N'000000001575', 1, N'1', N'50 Hükümdarlık', 10.8, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 12, 12, N'00', CAST(N'2019-10-08T15:22:48.000' AS DateTime), CAST(N'2019-10-08T15:22:39.897' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (17, N'bf8a9341-d45e-4834-a5fd-a2c4545e8e6f', N'V1', N'4fe2140d320adc1bbb9d4d351f2a82b5', N'MDO245230', N'000000001582', 1, N'1', N'Bim 10 TL Razer Gold Pin', 9, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 10, 10, N'00', CAST(N'2019-10-08T15:23:15.000' AS DateTime), CAST(N'2019-10-08T15:23:07.147' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (18, N'ae3899b8-2203-4fa6-a70c-76f37e31c4a7', N'V1', N'e377333c656cfe8a841f68cb9b0733ae', N'MDO245231', N'000000001583', 1, N'1', N'Bim 25 TL Razer Gold Pin', 22.5, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 25, 25, N'00', CAST(N'2019-10-08T15:24:17.000' AS DateTime), CAST(N'2019-10-08T15:24:09.253' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (19, N'f1cf3168-4c6b-47e9-94c2-4a9575b03754', N'V1', NULL, NULL, N'000000001586', 1, NULL, N'Bim 10.000 ZA', 0, N'TRY', NULL, 25, 25, NULL, CAST(N'2019-10-08T15:24:30.467' AS DateTime), CAST(N'2019-10-08T15:24:30.327' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (20, N'084c54c1-b1b4-4f3c-a9ce-a96253b1348f', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-08T15:25:03.363' AS DateTime), CAST(N'2019-10-08T15:25:03.347' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (21, N'5c03a3b7-7850-437d-b125-c8b6baea58bc', N'V1', N'7ccaf9bbf312ddadeff1bff518c142e2', N'MDO245480', N'000000001574', 1, N'1', N'25 Hükümdarlık', 5.4, N'TRY', N'52e7cf966b724749a7c4efadc3727ed7', 6, 6, N'00', CAST(N'2019-10-09T11:58:51.000' AS DateTime), CAST(N'2019-10-09T11:58:40.123' AS DateTime), N'', 0, 0, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (22, N'b8f3bd0d-ab01-4533-9356-497991178a62', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-10T15:27:27.413' AS DateTime), CAST(N'2019-10-10T15:27:27.117' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (23, N'f57c9fe1-7f83-465c-b0ea-2afbd9f9192b', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-10T15:29:12.493' AS DateTime), CAST(N'2019-10-10T15:29:12.463' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (24, N'e0e8b0ef-30d8-4e33-91bb-31572ab3739b', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-10T15:38:06.347' AS DateTime), CAST(N'2019-10-10T15:38:06.317' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (25, N'0f990695-0144-4ba0-a46f-ca65355e8dc4', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-10T15:39:58.553' AS DateTime), CAST(N'2019-10-10T15:39:58.523' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (26, N'6af0d028-2a92-45c1-90c7-b40664013e2c', N'V1', NULL, NULL, N'000000001585', 1, NULL, N'Bim 1.925 ZA', 0, N'TRY', NULL, 5, 5, NULL, CAST(N'2019-10-10T15:46:47.867' AS DateTime), CAST(N'2019-10-10T15:46:47.850' AS DateTime), NULL, 0, 0, N'OUR', NULL)
GO

SET IDENTITY_INSERT [dbo].[GameConfirmResponses] OFF
GO

Here is the ReconUpload table:

USE [GameAPI]
GO
/****** Object:  Table [dbo].[ReconUpload]    Script Date: 17.01.2023 16:24:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReconUpload](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[referenceId] [uniqueidentifier] NOT NULL,
	[productCode] [nvarchar](max) NULL,
	[quantity] [int] NOT NULL,
	[productDescription] [nvarchar](max) NULL,
	[totalPrice] [float] NOT NULL,
	
 CONSTRAINT [PK_dbo.ReconUpload] PRIMARY KEY CLUSTERED 
(
	[Id] 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].[ReconUpload] ON 
GO
INSERT [dbo].[ReconUpload] ([Id], [referenceId], [productCode], [quantity], [productDescription], [totalPrice]) VALUES (1, N'8c564293-5575-4fa0-8c38-0631a2e10aef', N'000000001570', 1, N'10 TL''lik Steam Bakiyesi', 10)
GO
INSERT [dbo].[ReconUpload] ([Id], [referenceId], [productCode], [quantity], [productDescription], [totalPrice]) VALUES (1, N'ae3899b8-2203-4fa6-a70c-76f37e31c4a7', N'000000001583', 1, N'Bim 25 TL Razer Gold Pin', 22.5)
GO
INSERT [dbo].[ReconUpload] ([Id], [referenceId], [productCode], [quantity], [productDescription], [totalPrice]) VALUES (1, N'b8f3bd0d-ab01-4533-9356-497991178a62', N'000000001585', 1, N'Bim 1.925 ZA', 5)
GO
INSERT [dbo].[ReconUpload] ([Id], [referenceId], [productCode], [quantity], [productDescription], [totalPrice]) VALUES (1, N'8c564293-5575-4fa0-8c38-0631a2e10aef', N'000000001570', 1, N'10 TL''lik Steam Bakiyesi', 10)
GO
INSERT [dbo].[ReconUpload] ([Id], [referenceId], [productCode], [quantity], [productDescription], [totalPrice]) VALUES (1, N'8c524233-4575-4fh0-8c36-0434v2e10beb', N'000000001572', 1, N'20 TL''lik Steam Bakiyesi', 20)
GO

SET IDENTITY_INSERT [dbo].[ReconUpload] OFF
GO

Cenk · January 19, 2023, 8:09am

Is this enough to compare 2 tables? RazerReconciliation is the table that I populated from excel. This is the periodic reconciliation of transactions. It seems to me that it may not be enough because the GameConfirmResponses table has all the transactions. There might be transactions that the GameConfirmResponses table has and the RazerReconciliation table does not. So somehow I need to check that scenario too.

Select ReferenceNo
From RazerReconciliation A
Where NOT EXISTS
( select referenceId from GameConfirmResponses B
Where A.ReferenceNo = B.referenceId
);

robert_volk · January 19, 2023, 4:11pm

You'd just need to invert the query you posted. If you want to combine those results:

;WITH miss_resp AS(SELECT ref_id=A.ReferenceNo FROM RazerReconciliation A
WHERE NOT EXISTS(SELECT 1 FROM GameConfirmResponses B WHERE A.ReferenceNo = B.referenceId))
,miss_recon AS (SELECT ref_id=x.referenceId FROM GameConfirmResponses x 
WHERE NOT EXISTS(SELECT 1 FROM RazerReconciliation y WHERE y.ReferenceNo = x.referenceId))
SELECT status='MissingResponse', ref_id FROM miss_resp
UNION ALL
SELECT status='MissingReconciliation', ref_id FROM miss_recon

Cenk · January 19, 2023, 4:28pm

Thank you @robert_volk, I wonder if there might be a performance issue on comparing 200K records?

robert_volk · January 19, 2023, 4:34pm

If the reference ID columns are indexed in both tables, then it should be fine. Are they both the same data type? If not, there will be an implicit conversion that would slow things down, as it might not use the indexes.

Cenk · January 19, 2023, 4:37pm

Yes, they are both the same data type, I will add indexes in both tables. Thank you again.

robert_volk · January 19, 2023, 5:57pm

If you're getting unwanted duplicates with the other version, you can try this:


;WITH miss_resp(ref_id) AS(SELECT ReferenceNo FROM RazerReconciliation EXCEPT SELECT referenceId FROM GameConfirmResponses)
,miss_recon(ref_id) AS (SELECT referenceId FROM GameConfirmResponses EXCEPT SELECT ReferenceNo FROM RazerReconciliation)
SELECT status='MissingResponse', ref_id FROM miss_resp
UNION ALL
SELECT status='MissingReconciliation', ref_id FROM miss_recon

Cenk · January 19, 2023, 6:07pm

This is a bonus

Cenk · January 20, 2023, 5:26am

Can I filter for a date interval? @robert_volk and also select TransactionDate?

robert_volk · January 20, 2023, 12:15pm

Sure:

;WITH miss_resp(ref_id, date_col) AS(SELECT ReferenceNo, TransactionDate FROM RazerReconciliation 
WHERE TransactionDate >='2022-01-01' and TransactionDate<'2023-01-01'
EXCEPT SELECT referenceId, TransactionDate FROM GameConfirmResponses
WHERE TransactionDate >='2022-01-01' and TransactionDate<'2023-01-01')
,miss_recon(ref_id, date_col) AS (SELECT referenceId, TransactionDate FROM GameConfirmResponses 
WHERE TransactionDate >='2022-01-01' and TransactionDate<'2023-01-01'
EXCEPT 
SELECT ReferenceNo, TransactionDateFROM RazerReconciliation
WHERE date_col >='2022-01-01' and date_col<'2023-01-01')
SELECT status='MissingResponse', ref_id, date_col FROM miss_resp
UNION ALL
SELECT status='MissingReconciliation', ref_id, date_col FROM miss_recon

Cenk · January 20, 2023, 1:05pm

You are very kind, thank you @robert_volk

Cenk · January 28, 2023, 2:03pm

Can I join Coupons on ConfirmResponseID with the GameConfirmResponses table in your SQL? Because I need to select Pin and Serial as well. @robert_volk

SELECT [Id]
,[ConfirmResponseID]
,[expiryDate]
,[Serial]
,[Pin]
FROM [GameAPI].[dbo].[Coupons]

robert_volk · January 28, 2023, 3:33pm

Not with the statements that use EXCEPT or UNION ALL. Those statements need to have the same columns in the same order in their SELECT lists.

You could do this:

;WITH miss_recon(ref_id, date_col) AS (SELECT referenceId, TransactionDate FROM GameConfirmResponses 
WHERE TransactionDate >='2022-01-01' and TransactionDate<'2023-01-01'
EXCEPT 
SELECT ReferenceNo, TransactionDateFROM RazerReconciliation
WHERE date_col >='2022-01-01' and date_col<'2023-01-01')
SELECT c.*
FROM miss_recon M
INNER JOIN GameConfirmResponses R ON M.ref_id=R.referenceId
INNER JOIN [GameAPI].[dbo].[Coupons] C on R.ConfirmResponseID=C.ConfirmResponseID

Cenk · January 28, 2023, 5:23pm

Unfortunately, the first query brings the Status column which is not in the second query. The first query has 35 records whereas the second one has 23. How can I fix?

First query:

;WITH miss_resp(ReferenceId, PurchaseDateTime) AS(SELECT ReferenceNo, TransactionDateTime  FROM RazerReconciliation WHERE TransactionDateTime >='2022-12-25 21:53:55.000' and TransactionDateTime<'2023-01-11 21:53:55.000' EXCEPT SELECT referenceId, purchaseStatusDate FROM GameConfirmResponses WHERE purchaseStatusDate >='2022-12-25 21:53:55.000' and purchaseStatusDate<'2023-01-11 21:53:55.000'),miss_recon(ReferenceId, PurchaseDateTime) AS (SELECT referenceId, purchaseStatusDate FROM GameConfirmResponses WHERE purchaseStatusDate >='2022-12-25 21:53:55.000' and purchaseStatusDate<'2023-01-11 21:53:55.000' EXCEPT SELECT ReferenceNo, TransactionDateTime FROM RazerReconciliation WHERE TransactionDateTime >='2022-12-25 21:53:55.000' and TransactionDateTime<'2023-01-11 21:53:55.000') SELECT Status='Found in Razer, Not Found in Our System', ReferenceId, PurchaseDateTime FROM miss_resp UNION ALL SELECT Status='Found in Our System, Not Found in Razer', ReferenceId, PurchaseDateTime FROM miss_recon

Second query:

;WITH miss_recon(ref_id, date_col) AS (SELECT referenceId, purchaseStatusDate FROM GameConfirmResponses 
WHERE purchaseStatusDate >='2022-12-25 21:53:55.000' and purchaseStatusDate<'2023-01-11 21:53:55.000'
EXCEPT 
SELECT ReferenceNo, TransactionDateTime FROM RazerReconciliation
WHERE TransactionDateTime >='2022-12-25 21:53:55.000' and TransactionDateTime<'2023-01-11 21:53:55.000')
SELECT M.ref_id As ReferenceId, C.Serial, C.Pin, M.date_col As 'Purchase Date'
FROM miss_recon M
INNER JOIN GameConfirmResponses R ON M.ref_id=R.referenceId
INNER JOIN Coupons C on R.Id = C.ConfirmResponseID

Cenk · January 29, 2023, 10:49am

I checked the data in both tables, it seems that your query is working @robert_volk

;WITH miss_recon(ref_id, date_col) AS (SELECT referenceId, purchaseStatusDate FROM GameConfirmResponses 
WHERE purchaseStatusDate >='2022-12-11 21:53:55.000' and purchaseStatusDate<'2023-01-11 21:53:55.000' and service = 'RAZER'
EXCEPT 
SELECT ReferenceNo, TransactionDateTime FROM RazerReconciliation
WHERE TransactionDateTime >='2022-12-11 21:53:55.000' and TransactionDateTime<'2023-01-11 21:53:55.000')
SELECT Status='Found in Our System, Not Found in Razer',M.ref_id As ReferenceId, C.Serial, C.Pin, M.date_col As 'Purchase Date'
FROM miss_recon M
INNER JOIN GameConfirmResponses R ON M.ref_id=R.referenceId
INNER JOIN Coupons C on R.Id = C.ConfirmResponseID