Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

February 03, 2003

Oracle v SQL Server, Part 8

Oracle has a feature that I can't find replicated in SQL Server.

When creating a table in Oracle, instead of specifying the column definitions you can specify an SQL query. This is really useful when cloning tables (and their contents) or creating partitions of large tables.

If anyone knows how to do this in SQL Server I would love to hear about it.

Update: Bermo gives the solution in the comments at the bottom of this post, use SELECT ... INTO. Thanks.

Posted by Andy Todd at February 03, 2003 10:31 AM


Use select into. You don't need to create the temp table beforehand - it will be created matching the opening select statement:

select * INTO #TABLENAME from [table]

if you don't want any rows copied (ie. only table definition), simply add: WHERE 1=0

Posted by: Bermo on February 3, 2003 12:57 PM