Post reply

Put output of a dynamic query using OpenQuery into a temporary table

  • May 18, 2023 at 9:07 pm

    #4192541

    Hello. I am tasked with converting some regular T-SQL queries to OpenQuery. I am very new to OpenQuery so learning on the fly. I have to do the query dynamically because of some of the variables involved. I have a very simplified version of what I am doing below. I want to know if I can output this into a temp table that I can run select statements against. Thank you.

     

    Set @tsql = 'Select Distinct * From OpenQuery(ServerName,' '

    Select Column1

    From Table

    ' ')'

    Exec(@tsql)

  • May 18, 2023 at 11:29 pm

    #4192649

    Disclaimer: I'm not an expert, but this worked for me... I didn't create mad dynamic SQL, but...

    ("Ingredient" is just some table I had in a database... nothing special.)  But  I could query the table once I finished inserting records into it.

    use bakery;

    DECLARE @TempSQL AS NVARCHAR(2000);

    SET @TempSQL = 'SELECT IngredientID, IngredientName, UnitPrice, IngredientWeight, IsFlour FROM Ingredient';
    SET @TempSQL = 'INSERT INTO #Ingredient(IngredientID, IngredientName, UnitPrice, IngredientWeight, IsFlour) ' + @TempSQL; 
    /*
    CREATE TABLE #Ingredient (
    [IngredientID] [int] NOT NULL,
    [IngredientName] [nvarchar](255) NULL,
    [UnitPrice] [money] NULL,
    [IngredientWeight] [real] NULL,
    [Volume] [real] NULL,
    [IsFlour] [bit] NULL,
    [VendorID] [int] NULL
    );
    */
    EXECUTE sp_executesql @TempSQL;

    SELECT * FROM #Ingredient;



Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply