SQL automatically converts decimal to numeric when select & insert

Question

I have SQL Server 2008 R2 and related SQL Server 2012. When I do the following

SELECT * INTO dbo.Local_table FROM dbo.Linked_table 

all decimal columns are automatically converted to numeric.

What is the reason and how can I get rid of it?

Answer 1

This is an automatic conversion that occurs with most SQL servers. You will find that this happens at least, but not limited to, server versions 2000-2014. I do not know how to get rid of this restriction, because it is a built-in server function that restricts arithmetic equations using query queries.

Here's another question / answer that might help you: T-SQL decimal precision

Here are some MSDN for clarification: https://msdn.microsoft.com/en-us/library/ms187746.aspx