SQL 如何在大查询中的sql上找到“字符串或二进制数据将被截断”错误

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

提问by Arif YILMAZ

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

我有一个巨大的INSERT INTO TABLE1 (....) SELECT .... FROM TABLE2声明。它给了我错误

"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 INSERTstatement.

我知道 TABLE2 中的一列对于INSERT语句中TABLE1 中的一列来说要大得多。

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?

我在每个表中有 100 多列。所以很难找出问题所在。有没有更简单的方法来解决这个问题？

回答by Zohar Peled

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

您可以查询Information_Schema.Columns两个表并检查内容长度的差异。

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

回答by David Rogers

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

@ZoharPeled 答案很棒，但对于临时表，您必须做一些不同的事情：

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)

回答by Donal

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') 

回答by Tony

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 articleto 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)

回答by Eralper

Merhaba Arif,

梅哈巴·阿里夫

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

我可以建议的是让比较更容易是从 sys.columns列出相关的表列定义并手动进行比较

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.

也许您可以使用字符串数据类型的列或具有 int、bigint 等大小的数值来限制返回的列表。

回答by Peter Henell

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

您可以从 information_schema.columns 查询两个表的定义，然后使用 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'

回答by Shafiq

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

;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

回答by Siamak Ferdos

Try:

尝试：

Select ID from TABLE2 where LEN(YourColumn) > SIZE