Eight Stars

Cannot insert the value NULL into column

I am joining Excel data and DB table data using tMap with ID as key. With this, DB table has to pull couple of column's more with the help of Key to Output but i am getting below error. Please suggest.

 

Cannot insert the value NULL into column 'MRN', table 'XYZ'; column does not allow nulls. INSERT fails.

 

Actually there's no null value in DB table data, but its still showing cant insert null value in MRN

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Eight Stars

Re: Cannot insert the value NULL into column

No... there is no null value for MRN in Demographics.

 

I have seen some mismatch in field length. MRN in Demographics is having length as String(20)  but the target table is having the length as nvarchar(50). Does this causing null values? 

13 REPLIES
Twelve Stars

Re: Cannot insert the value NULL into column

@samisyed80,since you were getting empty value for MRN column in XYZ table from source. you need to do the some default values.

 

row1.col==null?"ABC":row1.col

Manohar B
Forteen Stars TRF
Forteen Stars

Re: Cannot insert the value NULL into column

Seems the columns MRN of the table XYC is declared with the NOT NULL constraint so you MUST give a value for this field when you try to insert a new row. There is no choice.

Why do you have a null value if the input table has not?

Unable to answer while you don't share your job design with the tMap.


TRF
Eight Stars

Re: Cannot insert the value NULL into column

MRN is coming from the demographics table given below. Also find the tMap properties. Unfortunately, the target table is having zero rows.

 

 

Twelve Stars

Re: Cannot insert the value NULL into column

@samisyed80, you do not have the matched records between excel file and demographics ,since you were getting as null.

Manohar B
Eight Stars

Re: Cannot insert the value NULL into column

Manohar, Yes there is. Its ID a common field

Twelve Stars

Re: Cannot insert the value NULL into column

@samisyed80,then do you have null values for MNR in demographics ?

Manohar B
Eight Stars

Re: Cannot insert the value NULL into column

No... there is no null value for MRN in Demographics.

 

I have seen some mismatch in field length. MRN in Demographics is having length as String(20)  but the target table is having the length as nvarchar(50). Does this causing null values? 

Twelve Stars

Re: Cannot insert the value NULL into column

@samisyed80,if you are sure about there is no null values in MNR means ,your joining data is not matching please check.

Manohar B
Eight Stars

Re: Cannot insert the value NULL into column

No... there is no null value for MRN in Demographics.

 

I have seen some mismatch in field length. MRN in Demographics is having length as String(20)  but the target table is having the length as nvarchar(50). Does this causing null values? 

Twelve Stars

Re: Cannot insert the value NULL into column

@samisyed80,filed length is doesn't matter,when you have matched data in soruce and lookup table.

 

Manohar B
Twelve Stars

Re: Cannot insert the value NULL into column

@samisyed80,since your were data my not correct due to format,what kind of data it has in Id column in excel file?

Manohar B
Forteen Stars TRF
Forteen Stars

Re: Cannot insert the value NULL into column

By default, tMap use left outer join so if there is no matching row for a main row, MRN field will be null.
Use an inner join instead.

TRF
Eight Stars

Re: Cannot insert the value NULL into column

Sorry, i was pointing to wrong table. Issue has been resolved now. Thank you.