我参与了一个数据迁移项目。当我试图将数据从一个表插入到另一个表(SQL Server 2005)时，我得到以下错误:
编号8152,16层，状态13,1线 字符串或二进制数据将被截断。
源数据列与数据类型匹配，并且在目标表列的长度定义内，因此我不知道是什么原因导致了这个错误。
当前回答
这可能是一个具有挑战性的错误。以下是AmirCharania在https://connect.microsoft.com/SQLServer/feedback/details/339410/网站上发表的评论。
我调整了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)
其他回答
我写了一个有用的存储过程来帮助识别和解决使用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/questions/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。你可以在下面的链接中找到这段代码的最后一个versión，我们会互相帮助来改进它。GetFieldStringTruncate.sql
https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d
面对同样的问题。
日志含义源表中的列长度大于目标表中的列长度 源列长度- 50和 目标列长度- nvarchar(25)，并将其增加到nvarchar(50)，并且它工作
我今天遇到了这个问题，在我搜索这个信息量最小的错误消息的答案时，我还发现了这个链接:
https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name
所以微软似乎没有计划在短时间内扩展错误信息。
所以我转向了其他方法。
我把错误复制到excel中:
(1行受影响)
(1行受影响)
(1行受影响) 编号8152,16层，14号状态，13行 字符串或二进制数据将被截断。 声明已终止。
(1行受影响)
计算excel中的行数，接近导致问题的记录计数器…调整我的导出代码打印出SQL接近它…然后运行5 - 10个SQL插入问题SQL，并设法查明问题之一，看到字符串太长，增加该列的大小，然后大导入文件运行没有问题。
这是一种破解和变通方法，但当你别无选择时，你只能做你能做的。
这可能是一个具有挑战性的错误。以下是AmirCharania在https://connect.microsoft.com/SQLServer/feedback/details/339410/网站上发表的评论。
我调整了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)
是的，我也面临着这样的问题。
REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)
在这里，我将备注文件的长度从500更改为1000
推荐文章
- 如何将SQL Azure数据库复制到本地开发服务器?
- SQL Server 2008不能用新创建的用户登录
- 我如何移动一个redis数据库从一个服务器到另一个?
- SQL Server中User和Login的区别
- 在参数声明中，varchar(MAX)的大小是多少?
- 数据库性能调优有哪些资源?
- 为两列的组合添加唯一的约束
- SQL Server的隐藏特性
- 方括号[]在sql语句中有什么用?
- 对象'DF__*'依赖于列'*' -将int改为double
- 如何根据出生日期和getDate()计算年龄(以年为单位)
- 最有效的T-SQL方法垫一个varchar的左边到一定的长度?
- 如何改变SQL在“编辑前200行”在SSMS 2008
- 截断SQL Server日志文件的命令是什么?
- 作为变量的表名
