Creating new table from existing table

Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)you can use SELECT INTO

select * into <table name2> from <table name1 >|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH

Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >|||Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >
Duh - yeah - thanks Mallier - copy and paste error :o