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

Question

I am working on a query that copies the table structure from a linked server to a local database for a common list of tables.

However, for some reason, decimal data types are changing to numeric. It seems like this happens when you select on more connected servers. However, when I try to do the same on my local system, I could not replicate the problem.

In the environment where this error occurred, the SQL version of the local and linked server was different (10, 12, respectively). Not sure if this is related.

If anyone could shed light on this, that would be very grateful. Thanks.

The request is as follows:

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

I decided that my question updated the table with numbers to decimals after selecting using 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 answered.

Answer 2

NUMERIC and DECIMAL are interchangeable. But if this causes problems, the key may be to change these columns after creating the table. Doing this dynamically might look something like this:

 -- 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 everything NUMERIC to DECIMAL - it may not be what you want. In this case, you may need to create a dynamic CREATE TABLE statement.