Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: 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,
    Carl

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    below code will do the same job in sql2000
    Code:
    select fld1 into myNewTbl from myOldTbl
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    Great, I guess that's not as good as the ahem, the oracle way. Suppose you had a union query. How would that work?

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    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.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT * 
      INTO myNewTable 
      FROM (
    	SELECT fld1 FROM myOldTable1
    	 UNION
    	SELECT fld2 FROM myOldTable2) AS XXX
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    Quote Originally Posted by Brett Kaiser
    Code:
    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 myOldTable2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •