BulkMerge a DataTable with SqlBulkCopyOptions.KeepIdentity not keeping the identity from the DataTable

[iturribeitia]

BulkMerge a DataTable with SqlBulkCopyOptions.KeepIdentity is inserting new rows but with new Identity not keeping the identity from the DataTable.

rowsCount = connection.BulkMerge(fullTableName, dt, options: SqlBulkCopyOptions.KeepIdentity);

this the SQL table:
CREATE TABLE [dbo].[tGuarantor]
(
[GuarantorID] [int] NOT NULL IDENTITY(1, 1),
[GuarantorSSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrimaryPhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SecondaryPhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Salutation] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Deceased] [int] NULL,
[DOB] [date] NULL,
[DeceasedDate] [date] NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrimaryPhoneAsCell] [bit] NULL,
[SecondaryPhoneAsCell] [bit] NULL,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardPastDue] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tGuarantor] ADD CONSTRAINT [PK__tGuarant__92D36288F416C10E] PRIMARY KEY CLUSTERED ([GuarantorID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_tGuarantor_BySSN] ON [dbo].[tGuarantor] ([GuarantorSSN]) ON [PRIMARY]
GO

Library Version:

RepoDb.SqlServer.BulkOperations 1.1.6
RepoDb.SqlServer 1.1.5

[mikependon]

We have this kind issue if I remember twice already and we ended up investigating it as working as expected :) . We will investigate and revert back to you.

[iturribeitia]

We have this kind issue if I remember twice already and we ended up investigating it as working as expected :) . We will investigate and revert back to you.

Thanks a lot Mike for Your prompt response, I tested also using BulkInsert and It work fine with SqlBulkCopyOptions.KeepIdentity It insert the row in the table and keeps the value of the identity columm from the DataTable.

But BulkMerge with SqlBulkCopyOptions.KeepIdentity insert the non existing row but It generates a new identity, It is not keeping the value from the identity column from the DataTable.
Great Job with this amaizing Library !!!

[mikependon]

@iturribeitia thanks for pointing us out there. We will investigate and do the fix for you.

[dendism]

was this issue resolved? i have the same problem with my code:

	GlobalConfiguration.Setup().UseSqlServer();
	
	var listOfEmployees = new List<Employee>
	{
		new Employee
		{
			EmployeeId = 800,
			FirstName = "Milan",
			LastName = "Dendis"
		}
	};

	using var cnn = new SqlConnection(MainConnectionString);
	await cnn.EnsureOpenAsync();
	using var trn = cnn.BeginTransaction();

	await cnn.BulkMergeAsync(
		entities: listOfEmployees,
		hints: SqlServerTableHints.TabLock,
		options: SqlBulkCopyOptions.KeepIdentity,
		transaction: trn);
		
	await trn.CommitAsync();

instead of saving with given EmployeeId it always uses the identity value provided by sql server

[mikependon]

Have you tried this?
Link: https://repodb.net/enumeration/postgresql/bulkimportidentitybehavior#usability

[dendism]

Have you tried this? Link: https://repodb.net/enumeration/postgresql/bulkimportidentitybehavior#usability

that's postgres specific, i'm using mssql server. actually it would be neat to have option to enable identity inserts even for common insert/merge operations. i admit it's a bit edge-case, but from time to time i need to do it :)

[mikependon]

Never we had investigated this one yet. Surprisingly, BulkMerge has no coverage of KeepIdentity in the IntegrationTests. We need to dive down further into this to have it get fixed.

If BulkInsert is working fine, then I have a suspicion that the issue is somewhere during the creation of pseudo-temporary table.

Link: https://repodb.net/operation/bulkinsert#special-arguments

We will look on this one, but we are also okay to have this grab by the community.

[dendism]

Ok, i did some digging and came to this conclusions:

• first you create temporary table with SELECT INTO statement - in this table the record has correct id (as i specified it)
• next you compose the MERGE statement where all goes south :D
  • since the EmployeeId is both primary key and identity column, it's not part of the insertableFields list
  • as result of this it is not part of the INSERT clause of the MERGE statement
  • the MERGE statement is not surrounded with SET IDENTITY_INSERT [table] ON/OFF statements (in case of MSSQL server) to make it really work

As I see it now, the function GetBulkMergeSqlText needs one more parameter saying, if it should keep identities, and when it's set:

• primary key/identity column should be part of insertable column list
• the MERGE statement must be surrounded with SET IDENTITY_INSERT statements (as stated above for MSSQL server)

@mikependon hope this helps :)

[mikependon]

True, you nailed it and thanks a lot for digging. :)

By the way, a little correction on the temporary table. We should call it actually a pseudo-temporary table, as this pseudo-temporary table could actually a temporary (TEMP) table or memory or even a physical table on your database. There we insert the data and post process everything on the server.

There is a use-case of why we need to have a pseudo-table tweakable to be TEMP or MEMORY or PHYSICAL.
TEMP - saves the parallelism
MEMORY - saves the performance
PHYSICAL - it leverage all the defautl capability of the SQL Server. This is way faster than the TEMP but slower than MEMORY.

I am glad if you could make a PR on your findings :)