Как найти "String или двоичные данные будут усечены" ошибка на sql в большом запросе

У меня есть огромный оператор INSERT INTO TABLE1 (....) SELECT .... FROM TABLE2. Это дает мне ошибку

"Строковые или двоичные данные будут усечены".

Я знаю, что один из столбцов таблицы TABLE2 больше для одного столбца из TABLE1 в инструкции INSERT.

У меня более 100 столбцов в каждой таблице. Поэтому трудно решить эту проблему. Есть ли более простой способ понять это?

Ответ 1

Вы можете запросить Information_Schema.Columns для обеих таблиц и проверить разницу в длине содержимого.

Предполагая, что ваши таблицы имеют одинаковые имена столбцов, вы можете использовать это:

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)

Предполагая, что ваши таблицы имеют разные имена столбцов, вы можете это сделать и просто искать разницу

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

Ответ @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 required
    AND ISNULL(t1.Character_maximum_length, 0) < ISNULL(t2.Character_maximum_length, 0)

Ответ 3

Если имена столбцов совпадают, вы можете попробовать что-то вроде этого:

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

Ответ 4

Чтобы выяснить, в какой столбец данные помещаются слишком долго, я бы использовал следующий оператор для вывода результатов во временную таблицу.

SELECT ... 
INTO MyTempTable 
FROM Table2

Затем используйте пример запроса из этой статьи, чтобы получить максимальную длину данных для каждого столбца. Я приложил копию кода ниже.

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)

Ответ 5

Мерхаба Ариф,

То, что я могу предложить, состоит в том, чтобы упростить сравнение: перечислить связанные определения столбцов таблицы из sys.columns и вручную выполнить сравнение

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

Возможно, вы можете ограничить возвращаемый список столбцами типа данных строки или числовыми значениями с такими размерами, как int, bigint и т.д.

Ответ 6

Вы можете запросить определения двух таблиц из information_schema.columns, а затем получить diff с помощью 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'

Ответ 7

Попробуйте:

Select ID from TABLE2 where LEN(YourColumn) > SIZE

Ответ 8

Попробуйте это

    ;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

ИЗ ИНФОРМАЦИИ_SCHEMA.Columns ГДЕ Table_Name IN ('Table1', 'Table2') ) Выберите *, CHARACTER_MAXIMUM_LENGTH - NextValue в качестве отклонения из данных Где NextValue не равно NULL и (CHARACTER_MAXIMUM_LENGTH - NextValue) & lt;> 0 ORDER BY Column_Name, Character_maximum_length, Table_Name