create table as

I want to create table from
selecting records from another table In sql server 7.0
e.g
create table tablename
as
select * from another table/view.
How is it possible ??

Thanx in Advance.Hi To create a new table use

SELECT *
INTO fred
FROM bill
IT will fail if fred already exists

To empty and reuse then use

Truncate fred
INSERT INTO fred
SELECT *
FROM bill

Did I get the question right ?

Gerry|||I want to create table from
selecting records from another table In sql server 7.0
e.g
create table tablename
as
select * from another table/view.
How is it possible ??

Thanx in Advance.

WOW, you sound like a DB2 person...

Just don't use SELECT * if it's for Production.....|||Quite right about the "*" of course... I'm just a lazy typer :)

As for the DB2 guess:- not even close !|||Gerry, not you...dbtechzala

The syntax is close to a DB2 syntax....

Be careful of the TRUNCATE though...it'll mark every page as being deleted...

no where clause there....

CREATE TABLE [database . [schema1 ] . | schema1. ] tableName

Hi,
I try to create 2 schema as the syntax display's as below, but I get a error
as below. Do I have to change any parameters in SQL 2005?
And will I get any other problems in the future to use 2 schema?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
Create Schema.TableName (No Problem)
Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Create Databasename,Schema1.schema2.TableName (Error below)
Msg 117, Level 15, State 1, Line 13
The object name 'DatabasName.Schema1. Schema2.TableName' contains more than
the maximum number of prefixes. The maximum is 2.
Msg 319, Level 15, State 1, Line 84
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.A table can belong to only a single schema. The pipe in the syntax is a
choice, not a concatonation. Things like below are valid ways to specify
a table name
CREATE TABLE database..name
schema is the default schema for the user
CREATE TABLE database.schema.name
database is the database in use
CREATE TABLE schema.name
CREATE TABLE name
database is the database in use and schema is the default schema for the use
r
Dan

> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> as below. Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
> Create Schema.TableName (No Problem)
> Create Schema1.schema2.TableName (Problem SQL see schema1 as the
> database)
> Create Databasename,Schema1.schema2.TableName (Error below)
> Msg 117, Level 15, State 1, Line 13
> The object name 'DatabasName.Schema1. Schema2.TableName' contains more
> than
> the maximum number of prefixes. The maximum is 2.
> Msg 319, Level 15, State 1, Line 84
> Incorrect syntax near the keyword 'with'. If this statement is a
> common
> table expression or an xmlnamespaces clause, the previous statement
> must be
> terminated with a semicolon.|||Hi Dan,
I like to use 2 schema names as in the syntax.
Thank you,
Rune
"Dan Sullivan" wrote:

> A table can belong to only a single schema. The pipe in the syntax is a
> choice, not a concatonation. Things like below are valid ways to specify
> a table name
>
> CREATE TABLE database..name
> schema is the default schema for the user
>
> CREATE TABLE database.schema.name
>
> database is the database in use
> CREATE TABLE schema.name
>
> CREATE TABLE name
> database is the database in use and schema is the default schema for the u
ser
>
> Dan
>
>
>|||The syntax does not support two schema names. Look up "Documentation convent
ions"
in the BOL to see how this syntax is defined. If you want to use a "." in
a schema name then the name must be enclosed in [] or ""
CREATE SCHEMA [s1.s2]
CREATE TABLE [s1.s2].table_name
Dan
> Hi Dan,
> I like to use 2 schema names as in the syntax.
> Thank you,
> Rune
> "Dan Sullivan" wrote:
>|||> I like to use 2 schema names as in the syntax.
Yeesh. That is NOT what the syntax describes! | is or, not and! Meaning,
database.schema.table or schema.table. You cannot nest schemas. A schema
cannot own a schema. Period.

Create Table

Hi All
how can I Create Table (COPY STRUCTURE TO TableName Only ) From Another
Table In The Same Database
ThanksSELECT *
INTO newTable
FROM oldTable
WHERE 1 = 0;
Better yet, store your CREATE TABLE scripts in source control instead of
relying on this. Because you will not get any indexes, constraints, keys,
identity properties, statistics, extended properties, etc.
A
"TAHA" <TAHA105@.HOTMAIL.COM> wrote in message
news:uUamQAzBGHA.344@.TK2MSFTNGP11.phx.gbl...
> Hi All
> how can I Create Table (COPY STRUCTURE TO TableName Only ) From Another
> Table In The Same Database
> Thanks
>
>|||Thank you Aaron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OuEgpGzBGHA.984@.tk2msftngp13.phx.gbl...
> SELECT *
> INTO newTable
> FROM oldTable
> WHERE 1 = 0;
> Better yet, store your CREATE TABLE scripts in source control instead of
> relying on this. Because you will not get any indexes, constraints, keys,
> identity properties, statistics, extended properties, etc.
> A
>
> "TAHA" <TAHA105@.HOTMAIL.COM> wrote in message
> news:uUamQAzBGHA.344@.TK2MSFTNGP11.phx.gbl...
>