String or binary data would be truncated. The statement has been terminated. (lengths being the same)

Question

I'm getting the above error message and I'm aware it's "usually" when you are trying to insert data that is larger into columns that are smaller.

The problem is, the columns coming in aren't larger than the destination.

Take a look:

CREATE TABLE #temp (
    row_id INT identity(1, 1)
    ,error_ind BIT DEFAULT 0
    ,add_edit_del_ind VARCHAR(1) DEFAULT ''
    ,vendor_sap_load_temp_id INT
    ,vendor_id VARCHAR(10)
    ,vendor_name VARCHAR(35)
    ,title VARCHAR(15)
    ,name2 VARCHAR(35)
    ,name3 VARCHAR(35)
    ,name4 VARCHAR(35)
    ,address VARCHAR(60)
    ,city VARCHAR(40)
    ,district VARCHAR(40)
    ,STATE VARCHAR(3)
    ,zipcode VARCHAR(10)
    ,country VARCHAR(3)
    ,account_grp VARCHAR(4)
    ,industry_key VARCHAR(4)
    ,ext_code VARCHAR(10)
    ,systems_ind VARCHAR(10)
    ,remit_to_vendor_id VARCHAR(10)
    ,LANGUAGE VARCHAR(10)
    ,individual_us_tax_id VARCHAR(9)
    ,vendor_us_tax_id VARCHAR(9)
    ,vat_reg_cd VARCHAR(20)
    ,tax_office VARCHAR(10)
    ,tax_nbr VARCHAR(18)
    ,tax_jurisdiction VARCHAR(15)
    ,phone_nbr VARCHAR(30)
    ,email_addr VARCHAR(241)
    ,delete_ind BIT
    ,stk_id INT
    ,stk_role_id INT
    ,name_id INT
    )

Data is then inserted into this table and here's the MAX(LEN()) of all columns:

SELECT max(len(vendor_id)) vendor_id
    ,max(len(vendor_name)) vendor_name
    ,max(len(title)) title
    ,max(len(name2)) name2
    ,max(len(name3)) name3
    ,max(len(name4)) name4
    ,max(len(address)) address
    ,max(len(city)) city
    ,max(len(district)) district
    ,max(len(STATE)) STATE
    ,max(len(zipcode)) zipcode
    ,max(len(country)) country
    ,max(len(account_grp)) account_grp
    ,max(len(industry_key)) industry_key
    ,max(len(ext_code)) ext_code
    ,max(len(systems_ind)) systems_ind
    ,max(len(remit_to_vendor_id)) remit_to_vendor_id
    ,max(len(LANGUAGE)) LANGUAGE
    ,max(len(individual_us_tax_id)) individual_us_tax_id
    ,max(len(vendor_us_tax_id)) vendor_us_tax_id
    ,max(len(vat_reg_cd)) vat_reg_cd
    ,max(len(tax_office)) tax_office
    ,max(len(tax_nbr)) tax_nbr
    ,max(len(tax_jurisdiction)) tax_jurisdiction
    ,max(len(phone_nbr)) phone_nbr
    ,max(len(email_addr)) email_addr
    ,max(len(delete_ind)) delete_ind
FROM #temp

Results:

Here I try to insert into the destination:

INSERT INTO final_destination_table (
    vendor_id
    ,vendor_name
    ,honorific
    ,name2
    ,name3
    ,name4
    ,address
    ,city
    ,district
    ,STATE
    ,zipcode
    ,country
    ,account_grp
    ,industry_key
    ,ext_code
    ,systems_ind
    ,remit_to_vendor_id
    ,LANGUAGE
    ,vendor_ssn
    ,vendor_tin
    ,vat_reg_cd
    ,tax_office
    ,tax_nbr
    ,tax_jurisdiction
    ,phone_nbr
    ,email_addr
    ,delete_ind
    )
SELECT vendor_id
    ,vendor_name
    ,title
    ,name2
    ,name3
    ,name4
    ,address
    ,city
    ,district
    ,STATE
    ,zipcode
    ,country
    ,account_grp
    ,industry_key
    ,ext_code
    ,systems_ind
    ,remit_to_vendor_id
    ,LANGUAGE
    ,individual_us_tax_id
    ,vendor_us_tax_id
    ,vat_reg_cd
    ,tax_office
    ,tax_nbr
    ,tax_jurisdiction
    ,phone_nbr
    ,email_addr
    ,delete_ind
FROM #temp

I get the error:

And this is what the destination table looks like:

I don't see any issues with the lengths of the columns... can anyone spot the error?

Bruce Dunwiddie · Accepted Answer

wild guess, but I'm betting that you have a default constraint on the created_by column that grabs something like CURRENT_USER, which has a length longer than the varchar(10) definition of the created_by column. check all default constraints.

String or binary data would be truncated. The statement has been terminated. (lengths being the same)

Answers (2)

Related Questions