Create New Table From SELECT

[Thomas Parikka]

I'm trying to migrate a huge multi-table database (created far too
complex for its own good) into a three-table database, because it only
needs three tables. To do this, I am hoping to use JOINs to gather all
the information I need and INSERT it into a new database table (in MS
SQL 2008 R2). I can get the SELECT statement and JOIN to work properly
and return the results I need, but how to I "channel" the query
results into a new table with the columns returned by the SELECT?

This is my current query:

SELECT *
FROM Shipping_Info JOIN Customer_Info
ON Shipping_Info.Customer_Id = Customer_Info.Customer_Id
)

This returns a bunch of columns (Customer_Id, Order_Id, ST_Address1,
etc) and the relevant data in the query results but what I need to end
up with is a table with the columns/data returned by the query. How do
I go about this? What I've read on Wiki and via Google hasn't helped
much so far. Knowing the answer will help and if you have any
recommended reading for me I'd greatly appreciate it, I want to solve
this problem but learn from it too.

-Thomas

[Erland Sommarskog]

To create a table from a SELECT statement, you can add the INTO clause
before the FROM clause.

However, I can tell from there that it is not going to work out well,
since there are two columns Customer_Id, so you will have to replace
the SELECT * with an explicit column list.

And overall, I think it is better to issue a separate CREATE TABLE
statement than relying on SELECT INTO. If you are doing a proper
data model, you should decide what data types and what constraints
you need, and not take what you get from the old flawed model.

By the way, I don't know much about these tables, but if you plan to
have a table with both orderid and street address, I am not wholly
convinced that this is the right step, but I know little about the
database.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx