Behavior of "SELECT * INTO TableName FROM Source"?
Description: SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.
Syntax:
[ INTO new_table ]
1.new_table : Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen from the data source.
2. The format of new_table is determined by evaluating the expressions in the select list.
3. 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.
4. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.
5. To create the table in another database on the same instance of SQL Server, specify new_table as a fully qualified name in the form database.schema.table_name.
6. You cannot create new_table on a remote server; however, you can populate new_table from a remote data source. To create new_table from a remote source table, specify the source table using a four-part name in the form linked_server.catalog.schema.object in the FROM clause of the SELECT statement. Alternatively, you can use the OPENQUERY function or the OPENDATASOURCE function in the FROM clause to specify the remote data source.
Limitations and Restrictions:
The following limitations and restrictions apply to the INTO clause:
1. You cannot specify a table variable or table-valued parameter as the new table.
2. You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
3. 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.
4. SELECT...INTO cannot be used with COMPUTE.
5. The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.
6. 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 must create them after executing the SELECT...INTO statement.
7. Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.
Reference: http://msdn.microsoft.com/en-us/library/ms188029.aspx
Syntax:
[ INTO new_table ]
1.new_table : Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen from the data source.
2. The format of new_table is determined by evaluating the expressions in the select list.
3. 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.
4. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.
5. To create the table in another database on the same instance of SQL Server, specify new_table as a fully qualified name in the form database.schema.table_name.
6. You cannot create new_table on a remote server; however, you can populate new_table from a remote data source. To create new_table from a remote source table, specify the source table using a four-part name in the form linked_server.catalog.schema.object in the FROM clause of the SELECT statement. Alternatively, you can use the OPENQUERY function or the OPENDATASOURCE function in the FROM clause to specify the remote data source.
Limitations and Restrictions:
The following limitations and restrictions apply to the INTO clause:
1. You cannot specify a table variable or table-valued parameter as the new table.
2. You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
3. 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.
4. SELECT...INTO cannot be used with COMPUTE.
5. The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.
6. 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 must create them after executing the SELECT...INTO statement.
7. Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.
Reference: http://msdn.microsoft.com/en-us/library/ms188029.aspx
Comments
Post a Comment