Create a table based on the fields of a query

Question

Post reply

Create a table based on the fields of a query

jfrancisco

SSC-Addicted

Points: 423
More actions
September 24, 2010 at 10:31 am

#238773

Hi
I want to create a table based on the fields of a query. I want the new table has the exact fields retrieved from the query.
In Oracle is like this:
Create table <table_name>
as
select .......
and that's it. I was checking the sintaxis for creating a table in Sql server and I did not see any option like that.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it exists, but the syntax is a little different:

SELECT *

INTO NEWTABLENAME --creates this table based on the select query

FROM SOMETABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tim Parker SSCommitted Points: 1644 More actions · Answer 2

I would suggest the following if you are working with a large data set.

Step 1:

SELECT col_name_1, col_name_2, ....

INTO My_New_Table

FROM Old_Table

WHERE 1 = 0

Then

Step 2:

INSERT INTO My_New_Table

SELECT

col_name_1, col_name_2, ....

FROM Old_Table

The reason for the use of "WHERE 1 = 0" is so you do not hold locks on the SYSOBJECTS table in the database you're working with. Since 1 will never equal 0, the Step 1 query will only copy the table definition only, and will move no data. This can cause huge problems for you if you hold a lock on this table for an extended period of time.

😀

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Tim Parker (9/24/2010)
The reason for the use of "WHERE 1 = 0" is so you do not hold locks on the SYSOBJECTS table in the database you're working with. Since 1 will never equal 0, the Step 1 query will only copy the table definition only, and will move no data. This can cause huge problems for you if you hold a lock on this table for an extended period of time.

While it might not hold a lock with the WHERE 1 = 0, it might have significantly worse performance. SQL Server does optimizations for SELECT ... INTO that it doesn't, or can't, always do on an INSERT INTO. (SQL Server 2008 is better than SQL Server 2005 in this regard.)

Tim Parker SSCommitted Points: 1644 More actions · Answer 4

Locking the SYSOBJECTS table down for an extended period of time is about as worse performance as you can get.

If you do a SELECT * INTO DestinationTable FROM SourceTable, where the SourceTable is very large. You will be locking all of your database users out of the SYSOBJECTS table for the entire duration of the SELECT * INTO. It's better to isolate your table creation statement away from the data copy/move statement.

Of course this is all relative to whether you have users who will call you in a skinny minute because they can't access tables in SSMS because their is an apparent lock happening in the database they are accessing.

Please see this discussion for an example of one such user.

http://www.sqlservercentral.com/Forums/Topic984920-391-1.aspx

😀

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Tim Parker (9/25/2010)
Locking the SYSOBJECTS table down for an extended period of time is about as worse performance as you can get.
If you do a SELECT * INTO DestinationTable FROM SourceTable, where the SourceTable is very large. You will be locking all of your database users out of the SYSOBJECTS table for the entire duration of the SELECT * INTO. It's better to isolate your table creation statement away from the data copy/move statement.
Of course this is all relative to whether you have users who will call you in a skinny minute because they can't access tables in SSMS because their is an apparent lock happening in the database they are accessing.

Yep, I understand the ramifications, and in some cases getting the SELECT ... INTO to complete as fast as possible is the priority. Like I mentioned this is less of an issue with SQL Server 2008, both in terms of performance and log usage, which I hope to get production upgraded to soon. (Actually to SQL Server 2008 R2.)