Data-type changes in new table when using SELECT * INTO

Question

I'm working on a query that copies the table structure from a linked server into the local database for a generic list of tables.

However for some reason the decimal data types are getting changed to numeric. This only seemed to be happening when selecting into over linked servers. However when trying the same on my local system I could not replicate the problem.

The environment where this error happened the SQL version of the local and linked server were different (10, 12 respectively). Not sure if that's related.

If anyone could shed some light on this it would be much appreciated. Thanks.

The query is as per below:

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 and DECIMAL are interchangeable. But if this causes issues, the key may be to alter those columns after the table is created. Doing it dynamically could look something like:

-- 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

This will change all NUMERIC to DECIMAL - which may not be what you want. If that's the case, you might have to look into creating a dynamic CREATE TABLE statement.