Error while Creating table using Select Command

[Kevin]

Hi All,

I am new to MS SQL Server.I am using MS SQL 2000.I have a problem in
creating a table by using Select command.I have table called "test"
and i want to create another table with the same structure and rows.I
tried with the following command

create Table test1 as select * from test;

But it give an syntax error.I have tried the same command in Oracle
but i was working.Does MS SQL 2000 Server supports this kind of Query.

Please help me to solve the problem or any other methods to perform
this operation.

Thanks in Advance

Kevin

[David Portas]

Re: Error while Creating table using Select Command

You can use Query Analyzer to generate the CREATE TABLE statement
automatically from your exsiting table.

CREATE TABLE test1 (col1 INTEGER PRIMARY KEY, col2...)

INSERT INTO test1 (col1, col2,...)
SELECT col1, col2,...
FROM test

You can also use:

SELECT * INTO test1 FROM test

but the CREATE... INSERT method gives you more control over constraints,
etc.

--
David Portas
------------
Please reply only to the newsgroup
--

[Ryan]

Re: Error while Creating table using Select Command

SELECT * INTO NewTable FROM OldTable

This will create the same structure and take the data with it.

The other option is go to the design of your table, preview a script
of it, and then use this to build the new table (just change the table
name). Once you have the structure of the table, you can create / drop
this as needed within your query (i.e. Create Table ... SELECT
INTO...)

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<hrOdnSkox 9Ft2CaiRVn-vg@giganews.com >...[color=blue]
> You can use Query Analyzer to generate the CREATE TABLE statement
> automatically from your exsiting table.
>
> CREATE TABLE test1 (col1 INTEGER PRIMARY KEY, col2...)
>
> INSERT INTO test1 (col1, col2,...)
> SELECT col1, col2,...
> FROM test
>
> You can also use:
>
> SELECT * INTO test1 FROM test
>
> but the CREATE... INSERT method gives you more control over constraints,
> etc.[/color]