Use a while loop or cursor for querying row by row and insert results into temp
-
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.
-
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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) -
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.
-
The while loop in SQL begins with the WHILE keyword followed by the condition which returns a Boolean value i.e. True or False.
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.
-
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') -
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.
-
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?
-
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 ?
-
table1 exists on 5 different servers and want to combine the result into 1 temp table.
-
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- 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.
-
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
-
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(..."
-
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 19 total)
You must be logged in to reply to this topic. Login to reply