使用SELECT * INTO创建新表时，数据类型会发生改变

Question

我正在处理一个查询，将联接服务器上的表结构复制到本地数据库中的通用表列表中。

但是由于某些原因，十进制数据类型被更改为数字类型。这似乎仅在从联接服务器选择时发生。但是，当我尝试在我的本地系统上进行相同操作时，无法复制出问题。

该错误发生的环境中，本地和联接服务器的SQL版本不同(分别为10、12)。不确定是否相关。

如果有人能够解决这个问题，将不胜感激。谢谢。

查询如下：

WHILE (select count(*) from @tbls) > 0
BEGIN
    SELECT @id = 0, @tblname = '', @cols = '', @colSets = ''
    select top 1 @id = ID, @tblname = TableName, @PKField = PKField, @DataType = DataType from @tbls    

    if exists (select 1 from sys.tables where name = @tblname)
    begin
        delete from @tbls where ID = @id
        Continue;
    end

    exec('select * into '+ @tblname +' from [linkedserver].MyDatabase.dbo.'+@tblname + ' where 1 = 0')

    delete from @tbls where ID = @id
END

- stubs

---

在您的服务器内，SqlServer 知道字符集和格式是相同的。因此它会保留源。在不同的服务器之间，它通常会将所有数据类型隐式转换为每种数据类型的最高公共分母。由于您只是想创建一个没有数据的表，请参阅此线程 https://dev59.com/T3VD5IYBdhLWcg3wTZ1m。 - Amir Pelled

可能是http://stackoverflow.com/questions/33513776/sql-auto-converts-decimal-to-numeric-when-select-insert的重复问题。 - Andreea Dumitru

@AndreeaDumitru 可能是重复问题，但是那个答案并没有真正回答它。也没有接受答案... - TT.

@AndreaDumitru，你说得对，这确实是一个重复的问题。我在搜索时没有找到它。 我通过将数字更改回小数（因为我知道我不使用它们）来解决了这个问题。我希望有一个更可靠的解决方案。 但我很高兴有一些关于这个问题的解释。 - stubs

尝试将 select * into tblname from [linkedserver].MyDatabase.dbo.tblname where 1 = 0 更改为 select TOP 1 * into from [linkedserver].MyDatabase.dbo.tblname，以仅上传一行数据（之后不能截断表）。 - Konstantin Taranov

Answer 1

NUMERIC 和 DECIMAL 可以互换使用。但如果这会导致问题，关键可能是在创建表后修改这些列。动态执行可以如下：

-- Declare a dynamic SQL variable
DECLARE @sql VARCHAR(max)

WHILE (select count(*) from @tbls) > 0
BEGIN
    SELECT @id = 0, @tblname = '', @cols = '', @colSets = ''
    select top 1 @id = ID, @tblname = TableName, @PKField = PKField, @DataType = DataType from @tbls    

    if exists (select 1 from sys.tables where name = @tblname)
    begin
        delete from @tbls where ID = @id
        Continue;
    end

    exec('select * into '+ @tblname +' from [linkedserver].MyDatabase.dbo.'+@tblname + ' where 1 = 0')

    -- After table creation, use row-wise concatenation to create ALTER TABLE statements
    -- Change all numeric to decimal
    SELECT @sql = STUFF((SELECT CHAR(13) + CHAR(10)
                                + CONCAT('ALTER TABLE ALTER COLUMN ', [COLUMN_NAME], ' DECIMAL ', 
                                  '(' + CAST([numeric_precision] AS VARCHAR) + ', ' + CAST([numeric_scale] AS VARCHAR) + ');')
                         FROM   information_schema.columns c
                         WHERE  t.[TABLE_NAME] = c.[TABLE_NAME]
                                AND c.[DATA_TYPE] = 'numeric'
                         ORDER  BY c.[COLUMN_NAME]
                         FOR xml path(''), type).value('.', 'varchar(max)'), 1, 2, '')
    FROM   information_schema.tables t
    WHERE  t.[TABLE_NAME] = @tblname

    -- Run dynamic SQL statement (will sometimes be NULL, which is fine)
    EXEC(@sql)

    delete from @tbls where ID = @id
END

这将会改变所有的NUMERIC为DECIMAL - 这可能不是你想要的。如果是这种情况，你可能需要考虑创建一个动态的CREATE TABLE语句。

- Steven Hibble

Answer 2

我通过使用以下方法，在select into之后将表中的数字更改为小数，解决了我的问题：

    declare @lst table (Query varchar(300))

    insert into @lst
    select 'ALTER TABLE '+ TABLE_NAME + ' ALTER COLUMN '+ column_name +' '+
                'decimal' + '('+ cast(NUMERIC_PRECISION as varchar(20)) +','+ cast(NUMERIC_SCALE as varchar(20)) +')' as DataType
    from information_schema.COLUMNS
    where TABLE_NAME = 'Table_Name' and DATA_TYPE = 'numeric'

    while ((select count(*) from @lst) > 0)
    begin
        declare @s varchar(300)
        set @s = (select top 1 query from @lst)
        exec (@s)
        delete from @lst where query = @s
    end

感谢回复的人。

- stubs