SQL Server: preserve calculated fields with a SELECT INTO

Question

Hello,

My company performed a data migration recently (in a SQL Server 2005 database) and we noticed that some tables created with SELECT INTO didn't maintained the calculated fields of the original tables, but instead SQL Server created regular fields with the type returned by the original calculation. For example, suppose that you have this table:

create table Example (
 id int not null,
 quantity decimal(19,5) not null,
 price decimal(19,5) not null,
 total as price*quantity 
)

after doing a SELECT * INTO Example2 FROM Example you get:

create table Example2 (
 id int not null,
 quantity decimal(19,5) not null,
 price decimal(19,5) not null,
 total decimal(38,9) null
)

I fixed it dropping the bad fields and recreating them, but I want to know if there is a way of maintaining the calculated fields in the table created with the SELECT INTO (maybe with some special SQL Server configuration or using an alternative SQL command).

Thanks in advance.

Answer 1

My understanding is that the new table is created from the data types in the record set. However the record set does not contain information on how the resulting values are generated - therefore the computation formula is lost. (as extreme example, think of a view containing a GROUP BY as source for a SELECT INTO)

Answer 2

I don't think you would be able to do this with SELECT INTO - Just like if you were selecting against a view, it's just taking the results and pumping them to a new table.

You'll likely need to create the table with the computed columns first, then do a regular insert from the source table of the non-computed columns.

Answer 3

As a policy is it best to avoid using SLECT INTO to create tables especially in a migration. You not only will lose the computations, you will lose the identity fields and indexes, triggers and probably any defaults or constraints. In a migration you should always script out the tables you want to move including triggers, indexes etc.