IDENTITY_INSERT ISSUE

Question

IDENTITY_INSERT ISSUE

Rohit Kulkarni 731

Hello Team,

I am trying to load the data from azure blob storage to Dedicated sql Pool. For few of the tables the data is getting loaded dynamically and for few of the tables i am getting error :

ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'An explicit value for the identity column in table 'TABLE_NAME' can only be specified when a column list is used and IDENTITY_INSERT is ON.',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=An explicit value for the identity column in table 'TABLE_NAME' can only be specified when a column list is used and IDENTITY_INSERT is ON.,Source=.Net SqlClient Data Provider,SqlErrorNumber=8101,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=8101,State=1,Message=An explicit value for the identity column in table 'TABLE_NAME' can only be specified when a column list is used and IDENTITY_INSERT is ON.,},],'

My finding :

I have TRIED

SET IDENTITY_INSERT TABLE_NAME ON;

AS WELL AS

SET IDENTITY_INSERT TABLE_NAME OFF;

But still i am getting the same error .Please advise.

Regards

RK

Harishga 6,000 Reputation points Microsoft External Staff

2024-04-08T08:18:33.18+00:00

Hi @Rohit Kulkarni
Welcome to Microsoft Q&A platform and thanks for posting your question here.

The error message you're seeing occurs when you try to insert data into a table that has an identity column, and you want to insert a specific value into the identity column. By default, SQL Server automatically generates a unique value for the identity column for each row that is inserted. If you want to insert a specific value into the identity column, you need to turn on the IDENTITY_INSERT option for that table.

Once you've turned on the IDENTITY_INSERT option, you also need to specify a column list in your INSERT statement that includes the identity column. This tells SQL Server which columns you want to insert data into, including the identity column. If you don't specify a column list, SQL Server assumes that you want to insert data into all columns, including the identity column, which is not allowed.

After you've specified the column list and inserted the data, you need to turn off the IDENTITY_INSERT option for the table. This is important because leaving the IDENTITY_INSERT option on can cause issues with future inserts into the table.

Reference

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

I hope this information helps you. Let me know if you have any further questions or concerns.
Rohit Kulkarni 731 Reputation points

2024-04-08T09:39:17.77+00:00

In Azure data factory i am loading the data dynamically from copy Activity(Copy Command Method).At that time i am getting IDENTITY_INSERT error .

And how to mention the column list
Harishga 6,000 Reputation points Microsoft External Staff

2024-04-08T13:02:08.34+00:00

Hi @Rohit Kulkarni
Can you please follow the below link.
https://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a

I hope this information helps you. Let me know if you have any further questions or concerns.
Rohit Kulkarni 731 Reputation points

2024-04-08T13:12:46.35+00:00

Sorry to say this.Before opening this ticket i had gone through this link i tried to set IDENTITY_INSET IS ON still didn't worked out for me.

Thanks

rk
Harishga 6,000 Reputation points Microsoft External Staff

2024-04-10T10:17:31.3533333+00:00
Hi @Rohit Kulkarni
When you are using the Copy Command Method in Azure Data Factory to load data into a table with an identity column, you need to specify the column list in the mapping section of the copy activity.

Here's an example of how to specify the column list in the mapping section:

{ "source": { "type": "BlobSource" }, "sink": { "type": "SqlDWSink", "writeBatchSize": 10000, "writeBatchTimeout": "00:01:00", "sqlWriterCleanupScript": "DELETE FROM [dbo].[TABLE_NAME] WHERE [IdentityColumn] < 0" }, "enableStaging": false, "translator": { "type": "TabularTranslator", "columnMappings": "IdentityColumn:IdentityColumn, Column1:Column1, Column2:Column2" }, "parallelCopies": 1, "cloudDataMovementUnits": 32 }

In the columnMappings section, you need to specify the name of the identity column followed by a colon, and then the name of the column in the source data that corresponds to the identity column. After that, you can specify the rest of the columns in the same way.

Make sure that you specify the column list in the mapping section of the copy activity, and that you turn on the IDENTITY_INSERT option for the table before you run the copy activity. is there any documenation for that code

1 answer

Your answer

Harishga 6,000 Reputation points Microsoft External Staff

2024-04-08T08:18:33.18+00:00

Hi @Rohit Kulkarni
Welcome to Microsoft Q&A platform and thanks for posting your question here.

The error message you're seeing occurs when you try to insert data into a table that has an identity column, and you want to insert a specific value into the identity column. By default, SQL Server automatically generates a unique value for the identity column for each row that is inserted. If you want to insert a specific value into the identity column, you need to turn on the IDENTITY_INSERT option for that table.

Once you've turned on the IDENTITY_INSERT option, you also need to specify a column list in your INSERT statement that includes the identity column. This tells SQL Server which columns you want to insert data into, including the identity column. If you don't specify a column list, SQL Server assumes that you want to insert data into all columns, including the identity column, which is not allowed.

After you've specified the column list and inserted the data, you need to turn off the IDENTITY_INSERT option for the table. This is important because leaving the IDENTITY_INSERT option on can cause issues with future inserts into the table.

Reference

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

I hope this information helps you. Let me know if you have any further questions or concerns.
Rohit Kulkarni 731 Reputation points

2024-04-08T09:39:17.77+00:00

In Azure data factory i am loading the data dynamically from copy Activity(Copy Command Method).At that time i am getting IDENTITY_INSERT error .

And how to mention the column list
Harishga 6,000 Reputation points Microsoft External Staff

2024-04-08T13:02:08.34+00:00

Hi @Rohit Kulkarni
Can you please follow the below link.
https://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a

I hope this information helps you. Let me know if you have any further questions or concerns.
Rohit Kulkarni 731 Reputation points

2024-04-08T13:12:46.35+00:00

Sorry to say this.Before opening this ticket i had gone through this link i tried to set IDENTITY_INSET IS ON still didn't worked out for me.

Thanks

rk
Harishga 6,000 Reputation points Microsoft External Staff

2024-04-10T10:17:31.3533333+00:00

Hi @Rohit Kulkarni
When you are using the Copy Command Method in Azure Data Factory to load data into a table with an identity column, you need to specify the column list in the mapping section of the copy activity.

Here's an example of how to specify the column list in the mapping section:

{ "source": { "type": "BlobSource" }, "sink": { "type": "SqlDWSink", "writeBatchSize": 10000, "writeBatchTimeout": "00:01:00", "sqlWriterCleanupScript": "DELETE FROM [dbo].[TABLE_NAME] WHERE [IdentityColumn] < 0" }, "enableStaging": false, "translator": { "type": "TabularTranslator", "columnMappings": "IdentityColumn:IdentityColumn, Column1:Column1, Column2:Column2" }, "parallelCopies": 1, "cloudDataMovementUnits": 32 }

In the columnMappings section, you need to specify the name of the identity column followed by a colon, and then the name of the column in the source data that corresponds to the identity column. After that, you can specify the rest of the columns in the same way.

Make sure that you specify the column list in the mapping section of the copy activity, and that you turn on the IDENTITY_INSERT option for the table before you run the copy activity. is there any documenation for that code

Answer 1

Olaf Helper 47,436

IDENTITY_INSERT is a session related setting, not a global one. You have to issue it within the same session as you what to load data.

Rohit Kulkarni 731 Reputation points

2024-04-08T07:52:52.42+00:00

In Azure data factory i am loading the data dynamically.At that time i am getting IDENTITY_INSERT error .

Share via

IDENTITY_INSERT ISSUE

1 answer

Your answer