how to find "String or binary data would be truncated" error on sql in a big query
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
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)
@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)