SELECT INTO quirk I had no idea about
What is the structure of a table created from another via SELECT * INTO [Second] FROM [First]
?
A simple question, indeed, a basic one, but one that I have never asked myself until today. I honestly believed that the result is a generic table containing the same column names and types and their values and nothing else.
The answer, though, is strange. If the original column is NOT NULL, the resulting column will also be NOT NULL. If the original column has IDENTITY, the column in the second table will also have IDENTITY. And, as you know, you can't add or remove IDENTITY from existing columns without dropping and adding them back. The DEFAULT value, though, is not transferred.
The Microsoft reference page says:
The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.
...
When an existing identity column is selected into a new table, the new column inherits the
IDENTITY
property, unless one of the following conditions is true:
- The
SELECT
statement contains a join.- Multiple
SELECT
statements are joined by usingUNION
.- The identity column is listed more than one time in the select list.
- The identity column is part of an expression.
- The identity column is from a remote data source.
...
Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the
SELECT...INTO
statement. If these objects are required, you can create them after executing theSELECT...INTO
statement....
Specifying an
ORDER BY
clause does not guarantee the rows are inserted in the specified order....
When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time
SELECT...INTO
was executed.
So, my assumptions were wrong, but still what do you do when you want to achieve exactly that: create a new schema-less table with the same columns in terms of name and data type and value only? It's a bit ugly, but I have not found a better alternative. Just UNION ALL with a SELECT that has the same number of (nullable) columns and no rows. like this:
SELECT *
INTO [Second]
FROM [First]
UNION ALL
(SELECT NULL, NULL,NULL,NULL WHERE 1=0)
Assuming that the First table had four columns, this will result in a table having the same column names, types and values as the original table, but all columns will be nullable and with no identity.
Hope it helps!
Comments
Be the first to post a comment