字符串截断，ANSI_WARNINGS关闭

Question

使用Server 2008 (R2)。我在不同的数据库中有两个表(具有相同的列和数据类型--但大小不同)。我将值从一个插入到另一个，但问题是源表中有: nvarchar(200)，而目标表有一个类型为nvarhchar(100)的字段。源表中有大于100个字符的数据，因此错误：

*String or binary data would be truncated is thrown.*

我试着用

-- SourceServer is passed in at command prompt (batch)
    SET ANSI_WARNINGS OFF
    GO

    INSERT INTO DestinationTable(col1, col2,...) 
    SELECT col1, col2, ... 
    FROM $SourceServer.dbo.SourceTable 

    SET ANSI_WARNINGS ON
    GO

然而，这是抛出一个如下所示的错误："INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.“

我有什么办法解决这个问题吗？

Answer 1

您可以自己截断数据：

insert into desttable(destcolumn)
select left(sourcecolumn, 100) from sourcetable