SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2002
    Location
    Dubai
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Big but easy problem, pls help soon

    hi ppl
    problem regarding SQL.
    this is the situation. I have created a view. and, it fetched like over 8000 rows.
    The query runs in a query analyzer. but, i want to create a table based on this view. In oracle, we have
    create table <tablename> AS (select * from <viewname>)

    but, i am working with MS SQL server. and, nowww. this syntax doesnt work, it says "incorrect syntax next to AS"
    this is supported in oracle, and not sql server.
    Do we have another way out???
    pls help and reply ASAP

    or any other way to get around this..
    even, working wiht DTS packages is giving me problems.
    pls help

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Try this instead

    Code:
    SELECT field1, field2, field3
      INTO NewTableName
      FROM ViewName
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2002
    Location
    Dubai
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    u got it wrong
    this will add COLUMNS..
    i have like 6views. each having same number of columns and same headings, rather same datatypes
    i need a union of those 6 views onto a table.

    this is my difficulty
    pls help!
    and thanx for replying!

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure that you can create a TABLE from a VIEW. You can create another VIEW from the existing VIEWS if that's any help.

    Code:
    CREATE VIEW <view_name>
    AS
    SELECT * FROM <view1>
    UNION ALL
    SELECT * FROM <view2>

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    The code I provided WILL create a table based on the fields that are selected....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2002
    Location
    Dubai
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i dunt think its working, i converted teh views onto tables, and tehn a view of the table
    it worked!
    , but thanx guys!!!


Bookmarks

Posting Permissions

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