How to manage IDENTITY columns (MSSQL)

Six Stars

How to manage IDENTITY columns (MSSQL)

Hi all,
my question is how to manage identity columns. I explain my doubt:
usually I prefer to define my table key column with auto increment option (Identity in MSSQL). It's right recognized as BIGINT IDENTITY.
When use these tables as output for insert operations, talend tries to pass value (null) to the DB, then it gives me error because it's not permitted to set value to identity column. So I have to split my output to two components: one for insert (without ID column) and one for update (with ID column).
But I can't use the repository schema in the insert component, so I loose same useful function (propagate metadata changes,...).
Also I don't want to enable "IDENTITY INSERT" option in output component, because increment values are generated by DB (and others problem: MSSQL permit only one table with IDENTITY INSERT ON per session).
Any suggestions?
If a column is IDENTITY and identity insert is off, may be useful if output component automatically excludes that from insert statement? ask for a new functionality to bugtracker?
Community Manager

Re: How to manage IDENTITY columns (MSSQL)

The generated SQL statement is based on schema, and the schema must be defined at design time, so it is impossible to automatically excludes the IDENTITY column from insert statement. If you don't want IDENTITY Inserti option open, you have to do a map between the source data and target data to find out which data are to be inserted, and others are to updated. 
Talend | Data Agility for Modern Business


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables


Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables


Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema