Union all to create new table trouble, "missing key word" 1

[CTOROCK]

Hi I am trying to create a new table by combining 5 other tables. I can't seem to get my syntax right, although I've done it before like this. I'm using SQ:*Plus in Oracle. I get an erron on line two "missing key word". Any suggestions? Thanks in advance for any help. This is what I have:

SELECT *
INSERT INTO ALL_PRODUCT_NEW
FROM
(
SELECT *
FROM HI_PRODUCT_NEW
UNION ALL
SELECT *
FROM NW_PRODUCT_NEW
UNION ALL
SELECT *
FROM GA_PRODUCT_NEW
UNION ALL
SELECT *
FROM CO_PRODUCT_NEW
UNION ALL
SELECT *
FROM MA_PRODUCT_NEW
);


The greatest risk, is not taking one.

 

[CRoberts]

Your syntax is off. Do you care tablespace? The follow should work:

CREATE TABLE Test_Tab4
TABLESPACE TestTab_Ts
STORAGE (INITIAL 500M NEXT 50M
MAXEXTENTS UNLIMITED)
AS
SELECT *
FROM
(
SELECT *
FROM Test_Tab1
UNION ALL
SELECT *
FROM Test_Tab2
UNION ALL
SELECT *
FROM Test_Tab3
)
/

 

[CTOROCK]

That did it! Thanks, here's a star!

The greatest risk, is not taking one.