how to find "String or binary data would be truncated" error on sql in a big query

Question

I have a huge INSERT INTO TABLE1 (....) SELECT .... FROM TABLE2 statement. It gives me the error

"String or binary data would be truncated".

I know that one of the columns from TABLE2 is way bigger for one column from TABLE1 in the INSERT statement.

I have more than 100 columns in each table. So it is hard to find out the problem. Is there any easier way to figure this out?

Answer 1

You can query Information_Schema.Columns for both tables and check the difference in content length.

Assuming your tables have the same column names, you can use this:

SELECT t1.Table_Name, t1.Column_Name FROM INFORMATION_SCHEMA.Columns t1 INNER JOIN INFORMATION_SCHEMA.Columns t2 ON (t1.Column_Name = t2.Column_Name) WHERE t1.Table_Name = 'Table1' AND  t2.Table_Name = 'Table2' AND ISNULL(t1.Character_maximum_length, 0) < ISNULL(t2.Character_maximum_length, 0) 

Assuming your tables have different column names, you can do this and just look for the difference

SELECT Table_Name, Column_Name, Character_maximum_length FROM INFORMATION_SCHEMA.Columns WHERE Table_Name IN('Table1', 'Table2') ORDER BY Column_Name, Character_maximum_length, Table_Name