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

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

我有一个巨大的插入表1(....)选择....从表二。它给了我误差

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

我知道表2中的一列比INSERT语句中表1中的一列大得多。

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多列。所以很难找出问题所在。有更简单的方法来解决这个问题吗?

6 个解决方案

#1

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

你可以查询Information_Schema。两个表的列并检查内容长度的差异。

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

#2

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

#3

Try:

试一试:

Select ID from TABLE2 where LEN(YourColumn) > SIZE

#4

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

我的建议是，让比较更容易一些，从sys列出相关的表列定义。列并手动进行比较

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等大小的数值。

#5

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

您可以从information_schema查询这两个表的定义。列，然后用EXCEPT得到diff

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'

#6

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)

#1