Identity(Primary Key) Column Issue in Write Data In-DB Tool

Question

When I am trying to insert data to a table with Write Data In-DB Tool, an error is returned:

can only be specified when a column list is used and IDENTITY_INSERT is ON

I believe this error shows up because primary key was set to auto-incremental in SQL Server and Alteryx performed the query of :

INSERT INTO A SELECT * FROM TEMP without specifying the columns names to insert.

Somehow this error does not show up in OUTPUT tool(also wondering why). But In-DB is preferred in my case.

I found similar threads here:

Identity Columns Issue - Alteryx Community

Solved: InDB Write to MSSQL table with Identity column, er... - Alteryx Community

Solved: MS SQL Identity Column using In-Database Tools - Alteryx Community

This threads showed up in 2015, 2018 and 2022 (Don't know why two of them are marked as solved).

I appreciate anybody's help if there is a workaround.

Also if I can accept to unset primary key in SQL Server, is there a way I can generate key with Record ID tool that does not exist in current table?

AkimasaKajitani · Answer

Hi @SeanZhang

If you can make the "View" in SQL Server, it will work.

The View should be except the field which has the primary key.

SeanZhang · Answer

Hi,

Thanks for your reply but the thread link you shared did not have a solution.

SeanZhang · Answer

That primary key column was never selected. Write data In-DB don't have a choice but to match all columns that are in SQL server already(including primary key/identity column).