SQL Server: preserve calculated fields with a SELECT INTO

Question

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.

score 2 · Accepted Answer · answered Dec 29 '08 at 19:31

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.

answered Dec 29 '08 at 19:31

Anthony

720
4
10

Thanks, I supposed that but I want to get more opinions before discussing the problem with my boss. – Alberto Martinez Dec 30 '08 at 10:07

HLGEM · Answer 2 · 2017-04-20T13:55:47.003

1

As a policy is it best to avoid using SELECT INTO to create tables especially in a migration. You not only will lose the computations, you will lose 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.

edited Apr 20 '17 at 13:55

answered Dec 29 '08 at 20:15

HLGEM

94,695
15
113
186

Thanks for the tip, we don't use triggers (yet) in that database but losing indexes is certainly not good. – Alberto Martinez Dec 30 '08 at 10:09
Although you do lose computations and constraint objects, you don't lose identity or nullability. – Arin Taylor Apr 19 '17 at 02:54

score 0 · Answer 3 · answered Dec 29 '08 at 19:30

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)

score 0 · Answer 4 · answered Sep 30 '21 at 18:14

0

If you don't want to modify the Select Into statement, an alternative is to drop columns (Alter Table Drop Column) and recreate them (Alter Table Add) as computed columns.

answered Sep 30 '21 at 18:14

EduAime

1

1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/29964337) – Toni Sep 30 '21 at 19:26

SQL Server: preserve calculated fields with a SELECT INTO

4 Answers4