sql - 使用 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

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陈述。