Use a while loop or cursor for querying row by row and insert results into temp

  • sathwik.em91

    SSC Eights!

    Points: 834

    Hi,

    connectiondetails

    1.SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    2.SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    3.SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    4.SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    5.SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    I have the following table with 5 rows in it and need to loop through each row at a time and execute the openrowset command and insert the results into a temp table.Please assume that the connection string details are correct.

    I have tried using cursors but not get any correct results it says error.I think a while loop works better but not able to acheive the results.

    any help on this would be highly appreciated.

  • Phil Parkin

    SSC Guru

    Points: 244740

    I have the following table ...

    Where is this 'following table'?

    If you are just selecting from table1 all the time, why not select all of the rows you need in one hit?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • sgmunson

    SSC Guru

    Points: 110551

    Yep... there's more than a few details missing here...  no table description, and no description of the "why" behind doing all those individual OPENROWSET selects.   As there's no description of the table on which your alleged need for a loop is based, are we just supposed to read your mind as to what it is that needs done, exactly?   Please be a lot more detailed with regards to the task at hand...

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • sathwik.em91

    SSC Eights!

    Points: 834

    My bad I should be more clear on this part.

    Table name is connectiondetails

    Sno  and Sqlcommand are the columns

    SNo sqlcommand
    1 SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    2 SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    3 SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    4 SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    5 SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')

    answer for why -we are not supposed to use linked server concept here since we are trying for other alternatives.The server and port numbers are different so they are not connecting to same database.we are trying to run these select statements  one by one and then insert each of the result into a temp table.

    Also the reason I want to make it a row by row approach is even one of the query fails it should still pass on to the next query and get results from it.

    Please let me know for further questions.

    • This reply was modified 1 month, 1 week ago by  sathwik.em91.
  • mikestanley464

    Grasshopper

    Points: 11

    The while loop in SQL begins with the WHILE keyword followed by the condition which returns a Boolean value i.e. True or False.

    Bluestacks for PC Omegle apk

    The body of the while loop keeps executing unless the condition returns false. The body of a while loop in SQL starts with a BEGIN block and ends with an END block.

  • pietlinden

    SSC Guru

    Points: 62895

    So what happens if you create your temporary table and then do something like

    INSERT INTO #TempTable(col1)
    SELECT * FROM OPENROWSET('MSDASQL','Driver={MySQL ODBC 5.3 ANSI Driver};server=xxxxxxx;userid=xxxxx;pwd=;database=xxxxxx;port=xxxxx;','SELECT col1 from table1')
  • sathwik.em91

    SSC Eights!

    Points: 834

    Yes it is inserting results into the temp table.I need a dynamic way of handling all the individual select statements one by one,execute them and insert into temp table.

  • pietlinden

    SSC Guru

    Points: 62895

    Dynamic SQL?

    Create one variable to hold the static part of the INSERT statement, and then use the cursor to create the dynamic part, and use sp_executesql to execute the whole thing?

  • homebrew01

    SSC Guru

    Points: 55193

    Your 5 select examples look the same to me, so you will get the same result 5 times. Is that intended ?

    Or table1 exists on 5 different servers and you want to combine the result into 1 temp table ?

    • This reply was modified 1 month, 1 week ago by  homebrew01.
  • sathwik.em91

    SSC Eights!

    Points: 834

    table1 exists on 5 different servers and want to combine the result into 1 temp table.

  • Phil Parkin

    SSC Guru

    Points: 244740

    2020-08-13_09-37-14

    Which 'following table' and which 'connection string details'? Why are you avoiding providing more information? Also, where is the DDL for the temp table you want to create?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • homebrew01

    SSC Guru

    Points: 55193

    sathwik.em91 wrote:

    table1 exists on 5 different servers and want to combine the result into 1 temp table.

    It would be clearer if you specify 5 servers instead of server=xxxxxxx 5 times

    server=Server1, server=server2, server=server3....

    Help reduce the # of clarification questions & answers.

  • Steve Collins

    Ten Centuries

    Points: 1093

    Maybe have a look at this other thread where something similar was discussed.

    https://www.sqlservercentral.com/forums/topic/ignoring-error-and-continue/page/2/#post-3765149

    Not the connecting using openrowset but the loop and the error and continue

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jonathan AC Roberts

    SSCoach

    Points: 17337

    I think if you format the values in your table correctly you should be able to do it without a cursor or using dynamic SQL by using

    "CROSS APPLY OpenRowset(..."

  • Lynn Pettis

    SSC Guru

    Points: 442360

    No promises but this should at least get you started.

    -- The following table variables or for writing the code only, they should be replaced
    -- with your tables in the code that follows
    declare @Test table (sno int, sqlcommand nvarchar(max));
    declare @ConsolidateTable1 table (idcol int, datacol1 varchar(32));

    -- This is where the code starts
    declare @SQLCmd nvarchar(max);

    declare ProcessQrys cursor static, read_only, fast_forward for
    select sqlcommand from @Test;

    open ProcessQrys;

    while 1 = 1
    begin
    fetch ProcessQrys next into @SQLCmd;
    if @@FETCH_STATUS <> 0
    break;

    insert into @ConsolidateTable1
    exec sys.sp_executesql @SQLCmd;

    end

    close ProcessQrys;

    deallocate ProcessQrys;

Viewing 15 posts - 1 through 15 (of 20 total)

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