Я участвую в проекте переноса данных. Я получаю следующую ошибку, когда пытаюсь вставить данные из одной таблицы в другую таблицу (SQL Server 2005):
Msg 8152, уровень 16, состояние 13, строка 1
Строковые или двоичные данные будут усечены.
Столбцы исходных данных соответствуют типу данных и находятся в пределах определений длины столбцов таблицы назначения, поэтому я не понимаю, что может вызвать эту ошибку.
Вам нужно будет опубликовать определения таблиц для исходных и целевых таблиц, чтобы выяснить, где проблема, но , в нижней строке указано, что один из столбцов в исходной таблице больше, чем столбцы назначения. Возможно, вы меняете форматы таким образом, о котором вы не знали. Модель базы данных, с которой вы переезжаете, также важна для определения этого.
Проблема довольно проста: один или несколько столбцов в исходном запросе содержат данные, которые превышают длину столбца назначения. Простое решение - взять исходный запрос и выполнить Max(Len( source col )) для каждого столбца. I.e.,
Select Max(Len(TextCol1))
, Max(Len(TextCol2))
, Max(Len(TextCol3))
, ...
From ...
Затем сравните эти длины с длинами типов данных в таблице назначения. По меньшей мере один, превышает длину столбца адресата.
Если вы абсолютно уверены, что это не должно быть так, и все равно, если это не так, то другим решением является принудительное использование столбцов исходного запроса до их длины назначения (что приведет к усечению любых данных, которые тоже длинный):
Select Cast(TextCol1 As varchar(...))
, Cast(TextCol2 As varchar(...))
, Cast(TextCol3 As varchar(...))
, ...
From ...
Как уже говорили другие, один из типов данных столбцов в исходной таблице больше, чем столбцы назначения.
Простое решение - просто отключить предупреждение и разрешить усечение. Итак, если вы получаете эту ошибку, но уверены, что данные в вашей старой базе данных/таблице допустимы для усечения (обрезки по размеру), вы можете просто сделать следующее;
SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;
Как и выше, всегда не забывайте снова включать предупреждения. Надеюсь, это поможет.
Еще одна потенциальная причина для этого - установить настройку по умолчанию для столбца, который превышает длину столбца. Похоже, кто-то толкнул пальцем столбец длиной 5, но значение по умолчанию превысило длину 5. Это заставило меня сходить с ума, поскольку я пытался понять, почему он не работал ни на одной вставке, даже если все, что я вставлял, было один столбец с целым числом 1. Так как значение по умолчанию в схеме таблицы имело то, что нарушало значение по умолчанию, оно испортило все это - что, как мне кажется, привело нас к извлеченному уроку - избегайте наличия таблиц со значением по умолчанию в схеме.:)
Для остальных, также проверьте сохраненную процедуру. В моем случае в моей хранимой процедуре CustomSearch я случайно объявил недостаточно длины для моего столбца, поэтому, когда я ввел большие данные, я получил эту ошибку, хотя у меня большая длина в моей базе данных. Я просто изменил длину столбца в моем обычном поиске, ошибка исчезла. Это только для напоминания. Спасибо.
Это может быть сложной ошибкой. Вот несколько заметок, взятых из https://connect.microsoft.com/SQLServer/feedback/details/339410/, чтобы найти комментарий AmirCharania.
Я скорректировал ответ, данный AmirCharania, для данных, выбранных в фактическую таблицу, вместо временного. Сначала выберите ваш набор данных в таблицу разработки, затем выполните следующее:
WITH CTE_Dev
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TARGET TABLE NAME HERE, WITH SCHEMA')
)
,CTE_Temp
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TEMP TABLE NAME HERE, WITH SCHEMA')
)
SELECT *
FROM CTE_Dev D
FULL OUTER JOIN CTE_Temp T ON D.ColumnName = T.ColumnName
WHERE ISNULL(D.max_length, 0) < ISNULL(T.max_length, 999)
Сегодня я столкнулся с этой проблемой, и в поисках ответа на это минимальное информационное сообщение об ошибке я также нашел эту ссылку:
Итак, похоже, что в ближайшее время Microsoft не планирует расширять сообщение об ошибке.
Итак, я обратился к другим средствам.
Я скопировал ошибки, чтобы преуспеть:
(затронуты 1 строка (ы))
(затронуты 1 строка (ы))
(затронута 1 строка (ы)) Msg 8152, уровень 16, состояние 14, строка 13 Строка или двоичные данные будут проигнорированы. Заявление завершено.
(затронуты 1 строка (ы))
подсчитало количество строк в excel, приблизилось к счетчику записей, вызвавшему проблему... скорректировало мой код экспорта, чтобы распечатать SQL рядом с ним... затем запустил вставки размером 5-10 sql вокруг проблема sql и удалось определить проблему, см. строку, которая была слишком длинной, увеличить размер этого столбца, а затем большой файл импорта не стал проблемой.
Немного взлома и обходной путь, но когда вы ушли с очень небольшим выбором, вы делаете то, что можете.
Да, я также сталкиваюсь с такой проблемой.
REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)
Здесь у меня есть изменения ЗАМЕЧАНИЯ, отпечатанные длиной от 500 до 1000
это также может произойти, если у вас нет соответствующих разрешений
Я создал хранимую процедуру, которая анализирует исходную таблицу или запрос с несколькими характеристиками на столбец, среди которых минимальная длина (min_len) и максимальная длина (max_len).
CREATE PROCEDURE [dbo].[sp_analysetable] (
@tableName varchar(8000),
@deep bit = 0
) AS
/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/
DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
if CHARINDEX('from', @tableName)>0
set @isQuery=1
IF @intErrorCode=0 BEGIN
if @isQuery=1 begin
--set @tableName = 'USE '[email protected]+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
--replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
exec(@tableName)
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
set @intErrorCode=1
SET @errorMSG='Error generating temporary table from query.'
end
else begin
set @tableName='##tmpTableToAnalyse'
end
end
end
IF @intErrorCode=0 BEGIN
SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
select
c.column_name as [column],
cast(sp.value as varchar(1000)) as description,
tc_fk.constraint_type,
kcu_pk.table_name as fk_table,
kcu_pk.column_name as fk_column,
c.ordinal_position as pos,
c.column_default as [default],
c.is_nullable as [null],
c.data_type,
c.character_maximum_length as length,
c.numeric_precision as [precision],
c.numeric_precision_radix as radix,
cast(null as bit) as [is_unique],
cast(null as int) as min_len,
cast(null as int) as max_len,
cast(null as int) as nulls,
cast(null as int) as blanks,
cast(null as int) as numerics,
cast(null as int) as distincts,
cast(null as varchar(500)) as distinct_values,
cast(null as varchar(50)) as remarks
into ##tmpColumns'
if @isQuery=1 begin
SET @[email protected]+' from tempdb.information_schema.columns c, (select null as value) sp'
end
else begin
SET @[email protected]+'
from information_schema.columns c
left join sysobjects so on so.name=c.table_name and so.xtype=''U''
left join syscolumns sc on sc.name=c.column_name and sc.id =so.id
left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''
left join information_schema.key_column_usage kcu_fk on kcu_fk.table_name = c.table_name and c.column_name = kcu_fk.column_name
left join information_schema.table_constraints tc_fk on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
left join information_schema.table_constraints tc_pk on rc.unique_constraint_name = tc_pk.constraint_name
left join information_schema.key_column_usage kcu_pk on tc_pk.constraint_name = kcu_pk.constraint_name
'
end
SET @[email protected]+' where c.table_name = '''[email protected]+''''
exec(@tmpQ)
end
IF @intErrorCode=0 AND @deep = 1 BEGIN
DECLARE
@count_rows int,
@count_distinct int,
@count_nulls int,
@count_blanks int,
@count_numerics int,
@min_len int,
@max_len int,
@distinct_values varchar(500)
DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
select [column] from ##tmpColumns;
OPEN curTmp
FETCH NEXT FROM curTmp INTO @column_name
WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
' @count_rows=count(0), '+char(13)+char(10)+
' @count_distinct=count(distinct ['[email protected]_name+']),'+char(13)+char(10)+
' @count_nulls=sum(case when ['[email protected]_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
' @count_blanks=sum(case when ltrim(['[email protected]_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
' @count_numerics=sum(isnumeric(['[email protected]_name+'])),'+char(13)+char(10)+
' @min_len=min(len(['[email protected]_name+'])),'+char(13)+char(10)+
' @max_len=max(len(['[email protected]_name+']))'+char(13)+char(10)+
' from ['[email protected]+']'
exec sp_executesql @tmpQ,
N'@count_rows int OUTPUT,
@count_distinct int OUTPUT,
@count_nulls int OUTPUT,
@count_blanks int OUTPUT,
@count_numerics int OUTPUT,
@min_len int OUTPUT,
@max_len int OUTPUT',
@count_rows OUTPUT,
@count_distinct OUTPUT,
@count_nulls OUTPUT,
@count_blanks OUTPUT,
@count_numerics OUTPUT,
@min_len OUTPUT,
@max_len OUTPUT
IF (@count_distinct>10) BEGIN
SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
END ELSE BEGIN
set @distinct_values=null
set @tmpQ = N'USE '+DB_NAME()+';'+
' select @distinct_values=COALESCE(@distinct_values+'',''+cast(['[email protected]_name+'] as varchar), cast(['[email protected]_name+'] as varchar))'+char(13)+char(10)+
' from ('+char(13)+char(10)+
' select distinct ['[email protected]_name+'] from ['[email protected]+'] where ['[email protected]_name+'] is not null) a'+char(13)+char(10)
exec sp_executesql @tmpQ,
N'@distinct_values varchar(500) OUTPUT',
@distinct_values OUTPUT
END
UPDATE ##tmpColumns SET
is_unique =case when @[email protected]_distinct then 1 else 0 end,
distincts [email protected]_distinct,
nulls [email protected]_nulls,
blanks [email protected]_blanks,
numerics [email protected]_numerics,
min_len [email protected]_len,
max_len [email protected]_len,
[email protected]_values,
remarks =
case when @[email protected]_nulls then 'all null,' else '' end+
case when @[email protected]_distinct then 'unique,' else '' end+
case when @count_distinct=0 then 'empty,' else '' end+
case when @[email protected]_len then 'same length,' else '' end+
case when @[email protected]_numerics then 'all numeric,' else '' end
WHERE [column][email protected]_name
FETCH NEXT FROM curTmp INTO @column_name
END
CLOSE curTmp DEALLOCATE curTmp
END
IF @intErrorCode=0 BEGIN
select * from ##tmpColumns order by pos
end
IF @intErrorCode=0 BEGIN --Clean up temporary tables
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
end
IF @intErrorCode<>0 BEGIN
RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode
Я храню эту процедуру в основной базе данных, чтобы я мог использовать ее в каждой базе данных следующим образом:
sp_analysetable 'table_name', 1
// deep=1 for doing value analyses
И результат:
column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric,
guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric,
id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,
Я собираюсь добавить еще одну возможную причину этой ошибки только потому, что никто ее не упомянул, и это может помочь будущему человеку (поскольку ОП нашел свой ответ). Если в таблицу, в которую вы вставляете, есть триггеры, это может быть триггер, генерирующий ошибку. Я видел, что это произошло, когда определения полей таблицы были изменены, но таблицы аудита не были.
Да, "пинту в пол-пинту не поедут". Мне не повезло (по какой-либо причине) с различными SP, которые люди предложили, НО, пока две таблицы находятся в одной и той же БД (или вы можете получить их в один и тот же БД), вы можете использовать INFORMATION_SCHEMA. COLUMNS для нахождения ошибочного поля (ов), таким образом:
select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c1.CHARACTER_MAXIMUM_LENGTH,c2.table_name,c2.COLUMN_NAME, c2.DATA_TYPE,c2.CHARACTER_MAXIMUM_LENGTH
from [INFORMATION_SCHEMA].[COLUMNS] c1
left join [INFORMATION_SCHEMA].[COLUMNS] c2 on
c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME='MyTable1'
and c2.TABLE_NAME='MyTable2'
--and c1.DATA_TYPE<>c2.DATA_TYPE
--and c1.CHARACTER_MAXIMUM_LENGTH <> c2.CHARACTER_MAXIMUM_LENGTH
order by c1.COLUMN_NAME
Это позволит вам прокручивать вверх и вниз, сравнивая длину поля по мере продвижения. Прокомментированные разделы позволяют вам видеть (когда-то без комментирования, очевидно), если есть несоответствия типа данных, или, в частности, показывать те, которые отличаются по длине поля, потому что я слишком ленив для прокрутки - просто отдавайте себе отчет в том, что все это зависит от источника имена столбцов, соответствующие именам объекта.
SQL Server 2019, наконец, вернет более значимое сообщение об ошибке.
Двоичные или строковые данные будут усечены => улучшения сообщений об ошибках
если у вас есть эта ошибка (в производстве), неясно, какой из столбцов или строк возникает эта ошибка, и как ее точно определить.
Чтобы включить новое поведение, вам необходимо использовать DBCC TRACEON(460). Новый текст ошибки из sys.messages:
SELECT * FROM sys.messages WHERE message_id = 2628
2628 - Строковые или двоичные данные будут усечены в таблице '%. * Ls, column'%. * Ls. Усеченное значение: '%. * Ls.
Строковые или двоичные данные будут усечены: замена печально известной ошибки 8152
Это новое сообщение также передается в SQL Server 2017 CU12 (и в предстоящем SQL Server 2016 SP2 CU), но не по умолчанию. Вам нужно включить флаг трассировки 460, чтобы заменить идентификатор сообщения 8152 на 2628, либо на уровне сеанса, либо на уровне сервера.
Обратите внимание, что на данный момент даже в SQL Server 2019 CTP 2.0 необходимо включить тот же флаг 460 трассировки. В будущем выпуске SQL Server 2019 сообщение 2628 будет заменять сообщение 8152 по умолчанию.
SQL Server 2017 CU12 также поддерживает эту функцию.
Это обновление SQL Server 2017 содержит необязательное сообщение, содержащее следующую дополнительную информацию о контексте.
Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.Новый идентификатор сообщения - 2628. Это сообщение заменяет сообщение 8152 на любой вывод ошибки, если флаг фрейма 460 включен.
Я использовал пустую строку '' on on create table, а затем получаю ошибку 'Msg 8152, String или двоичные данные будут усечены' при последующем обновлении. Это происходило из-за значения обновления, содержащего 6 символов и превышающего ожидаемое определение столбца. Я использовал "ПРОСТРАНСТВО", чтобы обойти это только потому, что знал, что буду обновляться навалом после первоначального создания данных, т.е. Столбец не будет долго оставаться пустым.
SO BIG CAVEAT ЗДЕСЬ: Это не особо гладкое решение, но полезно в том случае, когда вы собираете набор данных, например. для одноразовых запросов интеллекта, где вы создаете таблицу для интеллектуального анализа данных, применяя некоторую массовую обработку/интерпретацию и хранение до и после результатов для последующего сравнения/разработки. Это часто встречается в моей работе.
Вы можете сначала заполнить, используя ключевое слово SPACE i.e.
select
Table1.[column1]
,Table1.[column2]
,SPACE(10) as column_name
into table_you_are_creating
from Table1
where ...
Последующие обновления для "column_name" из 10 символов или меньше (замените, если применимо) будут разрешены без возникновения ошибки обрезания. Опять же, я использовал бы это только в сценариях, подобных описанным в моем предостережении.
У меня была аналогичная проблема. Я копировал данные из одной таблицы в идентичную таблицу во всем, кроме имени.
В конце концов я сбросил исходную таблицу в временную таблицу с помощью инструкции SELECT INTO.
SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;
Я сравнил схему исходной таблицы с временной таблицей. Я обнаружил, что один из столбцов был varchar(4000), когда я ожидал varchar(250).
UPDATE: Вопрос о varchar (4000) можно объяснить здесь, если вы заинтересованы:
Для Nvarchar (Max) Я получаю только 4000 символов в TSQL?
Надеюсь, что это поможет.
Пожалуйста, попробуйте следующий код:
CREATE TABLE [dbo].[Department](
[Department_name] char(10) NULL
)
INSERT INTO [dbo].[Department]([Department_name]) VALUES ('Family Medicine')
--error will occur
ALTER TABLE [Department] ALTER COLUMN [Department_name] char(50)
INSERT INTO [dbo].[Department]([Department_name]) VALUES ('Family Medicine')
select * from [Department]
Эта ошибка возникает, когда столбец таблицы помещает ограничение [в основном длина]., Например. если схема базы данных для столбца myColumn CHAR (2), то, когда ваш вызов из какого-либо приложения будет вставлять значение, вы должны передать строку длиной две.
Ошибка в основном говорит об этом; строка длиной три и выше не соответствует размеру ограничения, заданному схемой базы данных. Именно поэтому SQL Server предупреждает и выдает ошибку потери данных/усечения.
Вот немного другой ответ. Имена столбцов и длины могут совпадать, но, возможно, вы указываете столбцы в неправильном порядке в инструкции SELECT. Скажем, tableX и tableY имеют столбцы с одинаковым именем, но в другом порядке
Я написал полезную процедуру хранения, чтобы помочь определить и устранить проблему усечения текста (String или двоичные данные будут усечены), когда используется инструкция INSERT SELECT. Он сравнивает поля CHAR, VARCHAR, NCHAR и NVARCHAR и возвращает поле оценки по полю в случае возможной причины ошибки.
EXEC dbo.GetFieldStringTruncate SourceTableName, TargetTableName
Эта хранимая процедура ориентирована на проблему усечения текста при выполнении инструкции INSERT SELECT.
Операция этой хранимой процедуры зависит от пользователя, ранее идентифицирующего инструкцию INSERT с этой проблемой. Затем вставьте исходные данные в глобальную временную таблицу. Рекомендуется инструкция SELECT INTO.
Вы должны использовать одно и то же имя поля таблицы назначения в псевдониме каждого поля оператора SELECT.
КОД ФУНКЦИИ:
DECLARE @strSQL nvarchar(1000)
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = OBJECT_ID(N'[dbo].[GetFieldStringTruncate]'))
BEGIN
SET @strSQL = 'CREATE PROCEDURE [dbo].[GetFieldStringTruncate] AS RETURN'
EXEC sys.sp_executesql @strSQL
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
------------------------------------------------------------------------------------------------------------------------
Description:
Syntax
---------------
dbo.GetFieldStringTruncate(SourceTable, TargetTable)
+---------------------------+-----------------------+
| SourceTableName | VARCHAR(255) |
+---------------------------+-----------------------+
| TargetTableName | VARCHAR(255) |
+---------------------------+-----------------------+
Arguments
---------------
SourceTableName
The name of the source table. It should be a temporary table using double charp '##'. E.g. '##temp'
TargetTableName
The name of the target table. It is the table that receives the data used in the INSERT INTO stament.
Return Type
----------------
Returns a table with a list of all the fields with the type defined as text and performs an evaluation indicating which field would present the problem of string truncation.
Remarks
----------------
This stored procedure is oriented to the problem of text truncation when an INSERT SELECT statement is made.
The operation of this stored procedure depends on the user previously identifying the INSERT statement with the problem. Then inserting the source data into a global temporary table. The SELECT INTO statement is recommended.
You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.
Examples
====================================================================================================
--A. Test basic
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[tblDestino]') AND TYPE IN (N'U'))
DROP TABLE tblDestino
CREATE TABLE tblDestino
(
Id INT IDENTITY,
Field1 VARCHAR(10),
Field2 VARCHAR(12),
Field3 VARCHAR(11),
Field4 VARCHAR(16),
Field5 VARCHAR(5),
Field6 VARCHAR(1),
Field7 VARCHAR(1),
Field8 VARCHAR(6),
Field9 VARCHAR(6),
Field10 VARCHAR(50),
Field11 VARCHAR(50),
Field12 VARCHAR(50)
)
INSERT INTO dbo.tblDestino
(
Field1 ,
Field2 ,
Field3 ,
Field4 ,
Field5 ,
Field6 ,
Field7 ,
Field8 ,
Field9 ,
Field10 ,
Field11 ,
Field12
)
SELECT
'123456789' , -- Field1 - varchar(10)
'123456789' , -- Field2 - varchar(12)
'123456789' , -- Field3 - varchar(11)
'123456789' , -- Field4 - varchar(16)
'123456789' , -- Field5 - varchar(5)
'123456789' , -- Field6 - varchar(1)
'123456789' , -- Field7 - varchar(1)
'123456789' , -- Field8 - varchar(6)
'123456789' , -- Field9 - varchar(6)
'123456789' , -- Field10 - varchar(50)
'123456789' , -- Field11 - varchar(50)
'123456789' -- Field12 - varchar(50)
GO
Result:
String or binary data would be truncated
*Here you get the truncation error. Then, we proceed to save the information in a global temporary table.
*IMPORTANT REMINDER: You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.
Process:
IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE ##TEMP
go
SELECT
[Field1] = '123456789' ,
[Field2] = '123456789' ,
[Field3] = '123456789' ,
[Field4] = '123456789' ,
[Field5] = '123456789' ,
[Field6] = '123456789' ,
[Field7] = '123456789' ,
[Field8] = '123456789' ,
[Field9] = '123456789' ,
[Field10] = '123456789' ,
[Field11] = '123456789' ,
[Field12] = '123456789'
INTO ##TEMP
Result:
(1 row(s) affected)
Test:
EXEC dbo.GetFieldStringTruncate @SourceTableName = '##TEMP', @TargetTableName = 'tblDestino'
Result:
(12 row(s) affected)
ORIGEN Nombre Campo ORIGEN Maximo Largo DESTINO Nombre Campo DESTINO Tipo de campo Evaluación
-------------------------- -------------------- ------------------------ ----------------------- -------------------------
Field1 9 02 - Field1 VARCHAR(10)
Field2 9 03 - Field2 VARCHAR(12)
Field3 9 04 - Field3 VARCHAR(11)
Field4 9 05 - Field4 VARCHAR(16)
Field5 9 06 - Field5 VARCHAR(5) possible field with error
Field6 9 07 - Field6 VARCHAR(1) possible field with error
Field7 9 08 - Field7 VARCHAR(1) possible field with error
Field8 9 09 - Field8 VARCHAR(6) possible field with error
Field9 9 10 - Field9 VARCHAR(6) possible field with error
Field10 9 11 - Field10 VARCHAR(50)
Field11 9 12 - Field11 VARCHAR(50)
Field12 9 13 - Field12 VARCHAR(50)
====================================================================================================
------------------------------------------------------------------------------------------------------------
Responsible: Javier Pardo
Date: October 19/2018
WB tests: Javier Pardo
------------------------------------------------------------------------------------------------------------
*/
ALTER PROCEDURE dbo.GetFieldStringTruncate
(
@SourceTableName AS VARCHAR(255)
, @TargetTableName AS VARCHAR(255)
)
AS
BEGIN
BEGIN TRY
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@colsUnpivotConverted AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @colsUnpivot = stuff((
SELECT DISTINCT ',' + QUOTENAME(col.NAME)
FROM tempdb.sys.tables tab
INNER JOIN tempdb.sys.columns col
ON col.object_id = tab.object_id
INNER JOIN tempdb.sys.types typ
ON col.system_type_id = TYP.system_type_id
WHERE tab.NAME = @SourceTableName
FOR XML path('')
), 1, 1, '')
,@colsUnpivotConverted = stuff((
SELECT DISTINCT ',' + 'CONVERT(VARCHAR(MAX),' + QUOTENAME(col.NAME) + ') AS ' + QUOTENAME(col.NAME)
FROM tempdb.sys.tables tab
INNER JOIN tempdb.sys.columns col
ON col.object_id = tab.object_id
INNER JOIN tempdb.sys.types typ
ON col.system_type_id = TYP.system_type_id
WHERE tab.NAME = @SourceTableName
FOR XML path('')
), 1, 1, '')
--https://stackoverflow.com/info/11158017/column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list
IF OBJECT_ID('tempdb..##TablaConMaximos') IS NOT NULL DROP TABLE ##TablaConMaximos
set @query
= 'SELECT u.d AS colname, MAX(LEN(u.data)) as [maximo_largo]
INTO ##TablaConMaximos
FROM
(
SELECT ' + @colsUnpivotConverted + '
FROM ' + @SourceTableName + '
) T
UNPIVOT
(
data
for d in ('+ @colsunpivot +')
) u
GROUP BY u.d'
PRINT @query
exec sp_executesql @query;
------------------------------------------------------------------------------------------------------------
SELECT --'Nombre de campo' = RIGHT('00' + ISNULL(CONVERT(VARCHAR,col.column_id),''),2) + ' - ' + col.name + ' '
--, 'Tipo de campo' = ISNULL(CONVERT(VARCHAR,upper(typ.name)),'') + '(' + ISNULL(CONVERT(VARCHAR,col.max_length),'') + ')'
[ORIGEN Nombre Campo] = tcm.colname
, [ORIGEN Maximo Largo] = tcm.maximo_largo
, [DESTINO Nombre Campo] = DESTINO.[Nombre de campo]
, [DESTINO Tipo de campo] = DESTINO.[Tipo de campo]
, [Evaluación] = CASE WHEN DESTINO.maximo_largo < tcm.maximo_largo THEN 'possible field with error' ELSE '' END
--, *
FROM tempdb.sys.tables tab
INNER JOIN tempdb.sys.columns col
ON col.object_id = tab.object_id
INNER JOIN tempdb.sys.types typ
ON col.system_type_id = TYP.system_type_id
RIGHT JOIN
(
SELECT column_id
, [Nombre de campo] = RIGHT('00' + ISNULL(CONVERT(VARCHAR,col.column_id),''),2) + ' - ' + col.name + ' '
, [Tipo de campo] = ISNULL(CONVERT(VARCHAR,upper(typ.name)),'') + '(' + ISNULL(CONVERT(VARCHAR,col.max_length),'') + ')'
, [maximo_largo] = col.max_length
, [colname] = col.name
FROM sys.tables tab
INNER JOIN sys.columns col
ON col.object_id = tab.object_id
INNER JOIN sys.types typ
ON col.system_type_id = TYP.system_type_id
WHERE tab.NAME = @TargetTableName
) AS DESTINO
ON col.name = DESTINO.colname
INNER JOIN ##TablaConMaximos tcm
ON tcm.colname = DESTINO.colname
WHERE tab.NAME = @SourceTableName
AND typ.name LIKE '%char%'
ORDER BY col.column_id
END TRY
BEGIN CATCH
SELECT 'Internal error ocurred' AS Message
END CATCH
END
Пока поддерживает только типы данных CHAR, VARCHAR, NCHAR и NVARCHAR. Вы можете найти последнюю версию этого кода в следующей ссылке ниже, и мы помогаем друг другу улучшать ее. GetFieldStringTruncate.sql
https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d