Search code examples
sql-servernvarchartruncated

Varchar and int columns join in sql

I tried this query 

insert into Dimension_DDTU (BI_Id, BI_Name, DBI_Id, dBI_Name)
    select
        ds.BI_ID,
        dvo.name,
        ds.BI_Id + '' + dm.DBI_Id as DBI_Id,
        dso.dname
    from 
        Census.dbo.SMaster sm
    full outer join 
        C.dbo.dis dso on dso.did = sm.did
    full outer join  
        C.dbo.Div dvo on dvo.dsid = dso.diid
    full outer join 
        b.dbo.DMap ds on ds.DOp_Id = dso.did
    full outer join 
        b.dbo.DsMap dm on dm.DOp_Id = sm.did

here BI_Id,DBI_Id data type is nvarchar(50) because I am storing values like this (001,002 ...)

and other columns which is in join are all int data type except 

BI_Id and DBI_Id

this shows an error

String or binary data would be truncated.

Solution

  • Error says you are having troubles with insertion.

    Here: Dimension_DDTU (BI_Id, BI_Name, DBI_Id, dBI_Name) one of column sized is not enough for storing inserted values.

    My chrystal ball says it is Dimension_DDTU.DBI_Id which is of same varchar(50) size whilst must be varchar(100) at least to accept ds.BI_Id + '' + dm.DBI_Id (varchar(50) + varchar(50)).

    If there will be a separator between those IDs then you should increaze size by size of that separator as well.