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

Question

Post reply

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

sathwik.em91

SSC Eights!

Points: 878
More actions
August 12, 2020 at 10:18 pm

#3780017

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.

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sgmunson SSC Guru Points: 110640 More actions · Answer 2

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sathwik.em91 SSC Eights! Points: 878 More actions · Answer 3

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 4 years, 4 months ago by sathwik.em91.

mikestanley464 Grasshopper Points: 11 More actions · Answer 4

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

https://vidmateapp.win

canli maç izle

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.

This reply was modified 4 years, 3 months ago by mikestanley464.
This reply was modified 3 years, 11 months ago by mikestanley464.
This reply was modified 3 years, 7 months ago by mikestanley464.

pietlinden SSC Guru Points: 63506 More actions · Answer 5

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: 878 More actions · Answer 6

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: 63506 More actions · Answer 7

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: 55548 More actions · Answer 8

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 4 years, 4 months ago by homebrew01.

sathwik.em91 SSC Eights! Points: 878 More actions · Answer 9

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 10

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

homebrew01 SSC Guru Points: 55548 More actions · Answer 11

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 SSCrazy Points: 2170 More actions · Answer 12

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: 18361 More actions · Answer 13

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: 442467 More actions · Answer 14

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;