我参与了一个数据迁移项目。当我试图将数据从一个表插入到另一个表(SQL Server 2005)时，我得到以下错误:
编号8152,16层，状态13,1线 字符串或二进制数据将被截断。
源数据列与数据类型匹配，并且在目标表列的长度定义内，因此我不知道是什么原因导致了这个错误。
当前回答
您需要发布源表和目标表的表定义，以便我们找出问题所在，但底线是源表中的一列比目标列大。这可能是您正在以一种您没有意识到的方式更改格式。要弄清楚这一点，您要使用的数据库模型也很重要。
其他回答
您需要发布源表和目标表的表定义，以便我们找出问题所在，但底线是源表中的一列比目标列大。这可能是您正在以一种您没有意识到的方式更改格式。要弄清楚这一点，您要使用的数据库模型也很重要。
我也遇到过类似的问题。我将数据从一个表复制到一个完全相同的表，除了名称。
最后，我使用SELECT into语句将源表转储到临时表中。
SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;
我比较了源表和临时表的模式。我发现其中一列是varchar(4000)，而我期待的是varchar(250)。
更新: 如果你感兴趣，varchar(4000)问题可以在这里解释:
对于Nvarchar(Max)，我只能在TSQL中获得4000个字符?
希望这能有所帮助。
我写了一个有用的存储过程来帮助识别和解决使用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)，并且它工作
是的，“一品脱的酒放进半品脱的锅里是不行的”。我对人们建议的各种sp没有太多的运气(无论出于什么原因)，但只要两个表在同一个DB中(或者您可以将它们放到同一个DB中)，您就可以使用INFORMATION_SCHEMA。列来定位错误字段，因此:
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
这将允许您上下滚动，比较字段的长度。注释的部分可以让您查看(显然是在未注释的情况下)是否存在数据类型不匹配，或者特别显示字段长度不同的数据类型(因为我懒得滚动)，只需注意，所有内容都基于与目标列名匹配的源列名。
推荐文章
- 选项(RECOMPILE)总是更快;为什么?
- 设置数据库从单用户模式到多用户
- 我如何转义一个百分比符号在T-SQL?
- SQL Server恢复错误-拒绝访问
- 使用Django South进行向后迁移
- 如何在SQL Server Management Studio中查看查询历史
- SQL Server索引命名约定
- 可以为公共表表达式创建嵌套WITH子句吗?
- SQL Server的NOW()?
- 在SQL Server中截断(不是四舍五入)小数位
- 如何在SQL Server数据库中更改列数据类型而不丢失数据?
- 保持简单，以及如何在一个查询中执行多个CTE
- 如何在Rails迁移中将可空列更改为不可空列?
- 如何使用TSQL截断数据库中的所有表?
- 如何从表中导出所有数据到可插入的sql格式?
