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