Re: INSERT INTO STATEMENT

"Peta Griffith" <peta_at_dataspeed.com.au> wrote in message news:<_8DA9.76154$g9.214986_at_newsfeeds.bigpond.com>...
> Hi All,
>
> I am have trouble coding a Insert Into Statement
>
> the below statements is basic example of the SQL
>
> INSERT INTO TEST1 ( ID,DESCRIPTION) SELECT TEST.ID,TEST.DESCRIPTION WHERE
> TEST.ID =5
>
> INSERT INTO TEST1 ( ID,DESCRIPTION) VALUES(SELECT TEST.ID,TEST.DESCRIPTION
> WHERE TEST.ID =5)
>
>
> i am trying to copy the detail from on table to another with exact
> structures
> if anyone knows the correct syntax or a better method for this it would be
> much appreciated
>
> Cheers
> Paul

Hi,

If you mean by copying "with exact structures" making a copy of the entire table then:

CREATE TABLE NEW_TEST1 AS SELECT * FROM OLD_TEST1; will give you an exact copy of OLD_TEST1 named NEW_TEST1. Or of course you can add a WHERE clause to the SELECT statement to only populate the new table with selected data:

CREATE TABLE NEW_TEST1 AS SELECT * FROM OLD_TEST1 WHERE OLD_TEST1.ID = 5;

Bear in mind that this is a minimal statement however and will create NEW_TEST1 in the default tablespace of the user who creates it and with default storage options of that tablespace. It is safer to add the TABLESPACE and STORAGE clauses to specify exactly where it goes and how it grows.

Regards,

Tim Kearsley
Database Manager
Milton Keynes Council Received on Thu Nov 14 2002 - 08:36:39 CST