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

Answer 2

To figure out which column the data is too long fit in, I would use following statement to output the results to a temp table.

SELECT ... 
INTO MyTempTable 
FROM Table2 

Then use the query example from this article to get the max data length of each column. I have attached a copy of the code below.

DECLARE @TableName sysname = 'MyTempTable', @TableSchema sysname = 'dbo'
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((SELECT 
    ' UNION ALL select ' + 
    QUOTENAME(Table_Name,'''') + ' AS TableName, ' + 
    QUOTENAME(Column_Name,'''') + ' AS ColumnName, ' +  
    CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext') THEN 'MAX(DATALENGTH(' 
         ELSE 'MAX(LEN(' 
         END + QUOTENAME(Column_Name) + ')) AS MaxLength, ' + 
    QUOTENAME(C.DATA_TYPE,'''') + ' AS DataType, ' + 
    CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) + ' AS DataWidth ' + 
    'FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C 
WHERE TABLE_NAME = @TableName 
AND table_schema = @TableSchema
--AND DATA_TYPE NOT IN ('XML','HierarchyID','Geometry','Geography')
ORDER BY COLUMN_NAME 
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')  

EXECUTE (@SQL)

Answer 3

@ZoharPeled answer is great, but for temp tables you have to do something a little different:

SELECT t1.Table_Name
    ,t1.Column_Name
    ,t1.Character_maximum_length AS Table1_Character_maximum_length
    ,t2.Character_maximum_length AS Table2_Character_maximum_length
FROM INFORMATION_SCHEMA.Columns t1
INNER JOIN tempdb.INFORMATION_SCHEMA.COLUMNS t2 ON (t1.Column_Name = t2.Column_Name)
WHERE t1.Table_Name = 'Table1'
    AND t2.Table_Name LIKE '#Table2%' -- Don't remove the '%', it's required
    AND ISNULL(t1.Character_maximum_length, 0) < ISNULL(t2.Character_maximum_length, 0)

Answer 4

If the column names are the same, you could try something like this:

SELECT 
    c1.name as ColumnName,
    c1.max_length AS Table1MaxLength,
    c2.max_length AS Table2MaxLength
FROM    
    sys.columns c1
    inner join sys.columns c2 on c2.name = c1.name
WHERE
    c1.object_id = OBJECT_ID('TABLE1') 
    c2.object_id = OBJECT_ID('TABLE2') 

Answer 5

You can query for the definitions of the two tables from information_schema.columns and then get the diff using EXCEPT

CREATE TABLE peter(a INT, b BIGINT, c VARCHAR(100));
CREATE TABLE peter2(a INT, b BIGINT, c VARCHAR(800));

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'peter'
EXCEPT 
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'peter2'

Answer 6

Merhaba Arif,

What I can suggest is to make comparison easier is to list the related table column definitions from sys.columns and make the comparison manually

SELECT * FROM sys.columns WHERE object_id = object_id('tablename')

Perhaps you can limit the returned list with string data type columns, or numeric values with sizes like int, bigint, etc.

Answer 7

Try:

Select ID from TABLE2 where LEN(YourColumn) > SIZE

Answer 8

Try this one

With Data as (
    SELECT 
    Table_Name, Column_Name, Character_maximum_length, Ordinal_Position, 
        LEAD(Character_maximum_length,1) Over(Partition by Column_Name Order by Table_Name) as NextValue
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name IN('Table1', 'Table2')
)
    Select * , CHARACTER_MAXIMUM_LENGTH - NextValue as Variance
    from Data 
    Where NextValue is not null and ( CHARACTER_MAXIMUM_LENGTH - NextValue) <> 0 
    ORDER BY Column_Name, Character_maximum_length, Table_Name 

Answer 9

See @aaron-bertrand's researched response on Stack Exchange's DBA site.

Basically

1. Turn on the tracing with DBCC TRACEON(460);
2. Execute your INSERT code
3. Turn on the tracing with DBCC TRACEOFF(460);

Using Aaron's example code:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
-- Drop if this is a test table with: DROP TABLE dbo.x;

DBCC TRACEOFF(460);

Answer 10

I am a beginner and I faced it during inserting names of Employees fname and lname. I did not specify the number of characters.

instead of writing this (wrong code):

create table Employee(
fname varchar ,
lname varchar
)

write this:

create table Employee(
fname varchar(10) ,
lname varchar(10)
)