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

[SeanZhang]

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]

Hi @SeanZhang ,

As mentioned in the last post of this thread, you can insert the data into the view except the primary key field.

MS SQL Identity Column using In-Database Tools

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/MS-SQL-Identity-Column-using-In-Databa...

It seems that this problem is bug of Alteryx.

>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?

Yes. You can make the logic using ROW_NUMBER() expression and so on.

[sbatra116]

@SeanZhang 

use select tool to ignore the primary key field and insert record in DB

[SeanZhang]

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).

[SeanZhang]

Hi,

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

[AkimasaKajitani]

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.