Creating a table from a subquery

Question

Post reply

Creating a table from a subquery

ffreire

SSC Rookie

Points: 29
More actions
March 21, 2002 at 12:51 am

#158177

Hi,
I would like to create and fill in a table with the resultset of a subquery.
Something like :
"CREATE TABLE tablename
AS
SELECT (*) from tablename2
WHERE condition, etc, etc.."
Is this possible ?
Thxs

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Not like you have it. Only this way

CREATE TABLE tablename (

ColumnDefinitionsHere

)

INSERT INTO tablename (ColumnListAcceptingDataHere) SELECT (*) from tablename2

WHERE condition, etc, etc..

Create table cannot add data to the table, this must be donw with an insert of somekind.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

ffreire SSC Rookie Points: 29 More actions · Answer 2

What I am actually looking for is to bypass the column definitions :

I just take the ones from my main source table and just fill it in with columns of another one.

The deal is that I used to work on a view but I would like to improve that view and just get rid of the (huge)source table my view is based on, so that I can set my indexes as I want. This new table is designed to be the Fact table of an OLAP cube.

So my statement should become something like this :

"

SELECT s.*

INTO dbo.NEW TABLE

FROM TABLESOURCE1 C

INNER JOIN TABLESOURCE2 S

ON C.Field1 = S.Field2

WHERE C.condition,...."

If I do this I get : "The column prefix 's' does not match with a table name or alias name used in the query."

Any idea ?

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Ok

SELECT * INTO dbo.NewTable FROM OldTable WHERE .....

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

ffreire SSC Rookie Points: 29 More actions · Answer 4

Okay I found it

Besides, my error is posted on the forum : 1st line

My database is case-sensitive so I had to post:

"SELECT S.*..." and not "SELECT s.*"

thxs

Edited by - ffreire on 03/21/2002 07:21:31 AM

rusjos Valued Member Points: 51 More actions · Answer 5

Hi all,

There is way to crack the problem and it is as follows

select (select count(*) from tab1) as tabname into tab2

This could return an error if the 'Select into/bulkcopy' is not enabled for the database on which you plan to execute the statement

Russel Joseph