One Star

IDENTITY_INSERT

I am reicieveing and error of
Cannot insert explicit value for identity column in table 'MEDISPAN_NDCHIST_QA' when IDENTITY_INSERT is set to OFF.
Cannot update identity column 'NDC_ID'.
Can someone please assist. The NDC_ID is set as the Identity in SQL. I have attached a few screen shots as well.
11 REPLIES
Community Manager

Re: IDENTITY_INSERT

Hi
As the error shows, it is not allowed to insert an explicit value for identity column when INDENTITY_INSERT is set to OFF. To resolve this error, you can either remove the NDC_ID column from the schema, you don't need to insert value for this column, as it is an identify column in table and the value will be generated automatically or check the option 'Turn on Identify insert' on tMssqlOutput component to allow to insert an explicit value for identity column.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: IDENTITY_INSERT

Hi,
Actually there are two method to solve your issue which are as follows:
1) No need to insert the values for IDENTITY column as it is a IDENTITY column so it will automatically insert the value for it as mention by above by Shong. So no need to map that column in our job.
2) If in case you still wanted to insert the value explicitly, there is a approach to do so as following:
a) First set the IDENTITY insert on by using following command / SQL
SQL: SET IDENTITY_INSERT TABLE_NAME ON;
b) After executing this it will allow you to insert the value in your IDENTITY column (NDC_ID)
c) Once inserting of your data is done you can again set IDENTITY insert to off by using following command /SQL
SQL: SET IDENTITY_INSERT TABLE_NAME OFF;

Best Regards,
Mayur
One Star

Re: IDENTITY_INSERT

ok so now i can now im stuck at another part. I can not get my scd_start or scd_end dates to show a value other than null. please see pictures below.
One Star

Re: IDENTITY_INSERT

Hi,
As per your "SCD compiler editor" snapshot which you have attached, it seems that you have put the scd_start column in the Unused section. It should be in Type0Field and it should be property mapped with scd_start.
Also check you mapping by using Edit Schema in your tLog_Row or you can also use "Sync Column" option from tLog_Row component.
Best Regards,
Mayur
One Star

Re: IDENTITY_INSERT

It still gives me the null value and the error code of parameter #1 has not been set
One Star

Re: IDENTITY_INSERT

Hi,
It means that you have not set the parameter properly, i.e. check you mapping by using edit schema option. Every column should be mapped properly.
It would be good if you can attach the latest snapshot of SCD compiler editor & the edit/view schema window.
Best Regards,
Mayur
One Star

Re: IDENTITY_INSERT

here are current views.
Please let me know if you need a differnt screen shot
One Star

Re: IDENTITY_INSERT

Hi,
It seems that you have made the End_Date as key, Remove the key from End_Date as End_Date is a fixed year value i.e a constant value hence it can not be a key.
Do this modification and let me know the outcome.
Best Regards,
Mayur
Community Manager

Re: IDENTITY_INSERT

Hi
The source keys should be the primary key that will be compared whether the keys already exist. remove the scd_end field from the source keys list.
The NDC_ID is the surrogate key in your case, don't put it into the type0 list; Remove the scd_start field from the type0 list. I suggest you to read this wiki article to learn more about Slowly_changing_dimension. Also, Talend provides a demo job for tMysqlSCD in this page:
https://help.talend.com/search/all?query=tMysqlSCD&content-lang=en
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: IDENTITY_INSERT

Hi Kwags,
This is for your reference:
Type 0: is not used frequently. Some dimension data may be overwritten and other may stay unchanged over time. This is most appropriate when no effort has been made to deal with the changing dimension issues.
Type 1: no history is kept in the database. New data overwrites old data. Use this type if tracking changes is not necessary. this is most appropriate when correcting certain typos, for example the spelling of a name.
Type2: the whole history is stored in the database. This type tracks historical data by inserting a new record in the dimensional table with a separate key each time a change is made. This is most appropriate to track updates, for example.

SCD Type 2 principle lies in the fact that a new record is added to the SCD table when changes are detected on the columns defined. Note that although several changes may be made to the same record on various columns defined as SCD Type 2, only one additional line tracks these changes in the SCD table.

The SCD schema in this type should include SCD-specific extra columns that hold standard log information such as:
-start: adds a column to your SCD schema to hold the start date. You can select one of the input schema columns as a start date in the SCD table.
-end: adds a column to your SCD schema to hold the end date value for a record. When the record is currently active, the end date is NULL or you can select Fixed Year Value and fill in a fictive year to avoid having a null value in the end date field.
-version: adds a column to your SCD schema to hold the version number of the record.
-active: adds a column to your SCD schema to hold the true or false status value. this column helps to easily spot the active record.
Type 3: only the information about a previous value of a dimension is written into the database. This type tracks changes using separate columns. This is most appropriate to track only the previous value of a changing column.
Best Regards,
Mayur
One Star

Re: IDENTITY_INSERT

Ok,
Thank you both for the great information. And thank you for bearing with me as i am new to all of this..
I changed somethings around and i am not getting the error in the pic below.