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

Within your server, SqlServer knows that the character sets and formats are the same. So it preserves the source. Between different servers, it will usually implicitly cast all datatypes to the highest common denominator for each datatype. Since you are just trying to create a table with no data, see this thread http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table — Amir Pelled, Feb 18 '16 at 10:23
Posible duplicate of http://stackoverflow.com/questions/33513776/sql-auto-converts-decimal-to-numeric-when-select-insert — Andreea Dumitru, Feb 18 '16 at 10:27
@AndreeaDumitru It might be a duplicate, but the one answer doesn't answer it really. Neither was the answer accepted... — TT., Feb 18 '16 at 13:00
@AndreaDumitru you are correct that it is a duplicate of that. Did not find it when I searched. I worked around the problem by changing numerics back to decimals (since I know I do not use them). I was hoping for a more solid solution. Still I'm glad there is some explanation to this problem. — stubs, Feb 19 '16 at 10:53
Try to change `select * into tblname from [linkedserver].MyDatabase.dbo.tblname where 1 = 0` to `select TOP 1 * into from [linkedserver].MyDatabase.dbo.tblname` for upload only one row (you cant truncate table after that) — Konstantin Taranov, Feb 19 '16 at 11:18

score 1 · Answer 1 · answered Feb 25 '16 at 21:09

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.

score 1 · Accepted Answer · answered Feb 26 '16 at 09:25

I solved my issue my updating the table the numerics to decimals after the select into using the below:

    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

Thanks for those who replied.

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

2 Answers2