Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    11

    Post Unanswered: Create a table from a select statement

    Good afternoon.
    Who knows how I can easily create a table where the column names come from another table and are created dynamically?

    That is to say. I need to create a new table where the columns, an unknown quantity, are created and given their names from an existing table.

    Theory would say:

    Create table Bin
    as select xyz from ABC
    group by xyz

    Unfortunately, MS Query Analyzer complains about the AS !!
    SQL 2000 server.

    Look forward to hearing about the correct way of doing this :-)

  2. #2
    Join Date
    May 2003
    Posts
    26
    Yep, SQL Server doesn't like the Oracle format for doing this.

    Try

    Select XYZ
    Into Bin
    From ABC
    Group by xyz

    This will create the correct column data types & lengths but will not create any of their corresponding dependencies. If this is to be part of a process you will also need some sort of logic to determine if that table already exists.

    Hope this helps.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    11
    Thanks, good but this only works into a #temp, otherwise get "run sp_dboption" error.
    This enters the data as rows. I need columns so that I can add the "real" data afterwards.
    Any more ideas ?
    Thanks very much

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Select into used to be discouraged because it was a nonlogged transaction that would invalidate your backup sequence. It may still be, though I couldn't find anything specific on this in Books Online.

    Run this statement:

    select DATABASEPROPERTY('YOURDBNAME', 'IsBulkCopy')

    If the result is 0, your database is not set to allow non-logged transactions. You may need to change the setting to use SELECT INSERT.

    blindman

  5. #5
    Join Date
    May 2003
    Posts
    26
    http://msdn.microsoft.com/library/de...err_1_1r94.asp

    Is there a specific reason the SELECT INTO option is not permitted in the DB you are working on? You may want to look into it. As for creating just the columns, add the clause WHERE 1 = -1.

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    11
    Interesting, very interesting.
    I shall have to look into this and find out why that DB has been set up in such a manner on Monday.
    I will use a work round, export to .csv gives me a nice long, 1500 comma sperated names, and then just do a normal create and let it all work for a while :-)
    What a nightmare, I hate work arounds, but it is a once off DB create.
    Thanks for your help, I shall stay tuned and hope that I can be of help to you in the near future.
    Take care and enjoy
    HandyMac

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    11
    Option No 2
    Open Fox 7, work with data, create the required table, import into SQL server.
    Life is great :-)
    Take care and have a great weekend,
    HandyMac

Posting Permissions

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