Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    50

    Unanswered: Create table and insert data based on the return of another query

    Not sure if this is possible, but I want to run a query on a linked Oracle database.
    Get the result.

    Build a new table (tmp or not), create the columns as per the fields returned by the query and insert the data.

    Is this possible?

    I've done some google searching and couldn't find anything, well tbh not sure what to search on.

    *** Edit: the reason I'm asking is that I'm lazy.. ***

    I've been tasked with two projects.

    1) - Importing the vmquery output from Netbackup.. About 50 columns of data and didn't fancy doing it all manually.

    2) - Importing the output of another query which again hasn't been formalised, but will be a lot of fields across several tables.

    *** Confession over ***



    Cheers.
    Last edited by MrRalphMan; 10-03-08 at 05:28.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    IF object_id('dbo.temp_table99') IS NOT NULL BEGIN
      DROP TABLE dbo.temp_table99
    END
    GO
    
    SELECT comma, separated, list, of, columns, not, the, dreaded, select, star
    INTO   dbo.temp_table99
    FROM   [linked_server_name].[database].[schema].[table]
    
    SELECT *
    FROM   dbo.temp_table99
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    50
    Cheers, that works for one of em...... Can a permi table be built from the temp one?

    Also can the same thing be done via bulk insert, or do you need the format file?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The above isn't a temporary table.
    Temporary tables are can be identified by their # (or ##) prefix.

    If the table exists then all you have to do is change the INSERT syntax.
    Code:
    INSERT INTO dbo.my_table99 (col1, col2)
    SELECT col1
         , col2
    FROM   dbo.my_other_table
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2007
    Posts
    50
    Ah ha... didn't spot that, cheers again.

Posting Permissions

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