Identity Columns While Inserting Data on DS 3.2

[Former Member]

I'd like update / insert a table with table comparision transform.  Target Table has an identity column.  

There is no problem with update but DI generates insert statement with identity column. How do i prevent this ?

I also try with auto correct load and do not update null columns property, it still add identity column to the insert statement.

Table columns

ID int identity

LGID int

LetterID int

Start int

Stop int

I compare Start and Stop values. Input keys are LGID and LetterID.

Generated sql statements :

UPDATE MyTable SET Start=value, Stop=Value WHERE LGID = LGID_value AND LetterID = LetterID_Value (This is ok)

INSERT MyTable(ID,LGID,LetterID,Start,Stop) VALUES(NULL,value,value,value,value)

I need an insert statement such as

INSERT MyTable(LGID,LetterID,Start,Stop) VALUES(value,value,value,value)

If i generate key for the ID column, DI tries to update the identity column. In this case, update statement raises error.

[Former Member · Accepted solution]

Hi,

In BODS jobs, while inserting a record into target table which has identity column, use the option "Use Input Key's" in target table. Have the property to "YES" for this " Use Input Key's" Option.

[Former Member · Accepted solution]

"Use Input Keys" is yes, "update key columns" is no.

But auto identity column is not key column both in source and target. My keys are two other columns   and also these columns are composite primary key in the target table

I try to generate key  and set the identity insert on, but in this time DI tries to update identity column and i do not know how to discard the column to be updated .

.

[darryl_goveas3 · Accepted solution]

Hi Birsen,

We have the exact same issue.  Did you ever find out how to resolve it?

Thanks,

Darryl

[Former Member · Accepted solution]

Hi Darryl,

Identity insert is an issue for DS development in DWH loading. What i normally do is if it a DWH loading, i will open the table from datastore and delete the surrogate key and fix a key in the input and use input keys in TC. That works.

Arun