No Create Table as Select from ?

SQLServer2k doesn't let me do a:
CREATE TABLE myNewTbl
AS
select fld1 from myOldTbl
In oracle it works.
Thanks,
Carlbelow code will do the same job in sql2000

select fld1 into myNewTbl from myOldTbl|||Great, I guess that's not as good as the ahem, the oracle way. Suppose you had a union query. How would that work?|||what union query? u meant something like,

select fld1 into myNewTbl from (select fld1 from table1 union select fld1 from table2) as tm
post ur union query.|||Ya sorry, I should've been more specific.

In oracle I would:

CREATE TABLE myNewTable AS
SELECT fld1 FROM myOldTable1
UNION
SELECT fld2 FROM myOldTable2

Mostly i use this to get a roughed out table structure, then set up my append queries, so your 1st reply did the job, I just used the 1st SELECT.

Thanks,
Carl|||SELECT *
INTO myNewTable
FROM (
SELECT fld1 FROM myOldTable1
UNION
SELECT fld2 FROM myOldTable2) AS XXX|||SELECT *
INTO myNewTable
FROM (
SELECT fld1 FROM myOldTable1
UNION
SELECT fld2 FROM myOldTable2) AS XXX

Why not just:

SELECT fld1
INTO myNewTable
FROM myOldTable1
UNION
select fld2
FROM myOldTable2sql