Talend: tmapSQLOutput – Skipping Identity Column during Inserts

Background

A quick session on skipping system columns (Identity, timestamp) when inserting data into DB tables using Talend – Open Studio

Error Messages

Here is the error message we get:

Text:


Cannot insert explicit value for identity column in table 'account' when IDENTITY_INSERT is set to OFF.

Image:

CannotInsertExplictValues

Resolution

Component – tMSSqlOutput

Choose Component

In the Canvas, choose the tMSSQLOutput component.

accesstMSSqlOutputComponent

 

Basic Settings

BasicSettings

 

 

Make sure of the following:

  • “Turn on Identity insert” is off / unchecked

 

Advanced Settings

Here is what the “Advanced settings” screen looks like initially.

AdvancedSettings-Initial

 

Use field Options

To get more customization we have to check the “Use field Options” checkbox.

 

Use field Options

Once the “Use field Options” checkbox is checked, the individual fields are listed.

UseFieldOptions-Initial

 

Quick Explanation:
  • We can see that all the fields have the “Updatable” and “Insertable” checkboxes checked.
  • It is nice that we are also able to explicitly specify the “Update Key” and “Deletion Key”.  Being able to so will be useful for tables missing a primary key.

 

Use field Options – After

To specify that our Identity Column (id), should be skipped during inserts and updates, we uncheck the row for the “Updatable” and “Insertable” columns.

UseFieldOptions-UncheckIdentityColumns

Retry Execution

We saved the changes, and retried the execution and we are good.

 

SQL Profiler

While failing and during success run, we captured the DB interaction using SQL Server Profiler.

Identity Column Referenced


SELECT @@MAX_PRECISION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647

SET IMPLICIT_TRANSACTIONS ON

declare @p1 int
set @p1=1
exec sp_prepare
       @p1 output
      ,N'@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000)'
      ,N'INSERT INTO [sf].[account] ([id],[AccountNumber],[AccountName],[WebSite]) VALUES ( @P0 , @P1 , @P2 , @P3 )',1

select @p1

exec sp_execute 1,0,N'CC978213',N'GenePoint',N'www.genepoint.com'

exec sp_execute 1,0,N'CD355119-A',N'United Oil & Gas, UK',N'http://www.uos.com'

exec sp_execute 1,0,N'CD355120-B',N'United Oil & Gas, Singapore',N'http://www.uos.com'

exec sp_execute 1,0,NULL,N'sForce',N'www.sforce.com'

IF @@TRANCOUNT > 0 COMMIT TRAN


Identity Column Skipped


SELECT @@MAX_PRECISION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647

SET IMPLICIT_TRANSACTIONS ON

declare @p1 int
set @p1=1
exec sp_prepare
       @p1 output
      ,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000)'
      ,N'INSERT INTO [sf].[account] ([AccountNumber],[AccountName],[WebSite]) VALUES ( @P0 , @P1 , @P2 )'
      ,1
select @p1

select @p1

exec sp_execute 1,N'CC978213',N'GenePoint',N'www.genepoint.com'

exec sp_execute 1,N'CD355119-A',N'United Oil & Gas, UK',N'http://www.uos.com'

exec sp_execute 1,N'CD355120-B',N'United Oil & Gas, Singapore',N'http://www.uos.com'

exec sp_execute 1,NULL,N'sForce',N'www.sforce.com'

IF @@TRANCOUNT > 0 COMMIT TRAN

IF @@TRANCOUNT > 0 COMMIT TRAN

Quick Explanation:

  1. Failing
    • When we were failing, the id column was included in the list of columns and values passed to SQL Server
    • A value of 0 was assigned to the id column
  2. Successful
    • Once successful, the id column was skipped altogether

 

Credit

Crediting Bekwam

Creating a SQL Server Identity Column with Talend Open Studio
http://bekwam.blogspot.com/2011/12/creating-sql-server-identity-column.html

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s