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

Viewing 5 posts - 16 through 19 (of 19 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

August 14, 2020 at 8:55 pm

#3780610

So, no response after actual code is provided?

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

I should have replied yesterday,but was busy.So I tried a while loop it works for querying data but the data insertion to temp table part fails.I am still working on this part but the results are not as expected.

Thanks for your input and appreciate your time!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

August 14, 2020 at 9:08 pm

#3780612

So, post the code you are working with.

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

CREATE PROCEDURE usp_ors 
                     @sqlquery VARCHAR(MAX)
AS BEGIN

SET NOCOUNT ON
BEGIN TRY


--Query connectionstring details from table
IF OBJECT_ID('tempdb.dbo.##mappingtable', 'U') IS NOT NULL
  DROP TABLE ##mappingtable; 
 SELECT   
  DBID
, REPLACE(REPLACE(CONVERT(VARCHAR(256),connection_string),'userid','user'),'pwd','password')    AS connectionstring
INTO ##mappingtable
FROM      connectionstringdetails



--Query start
DECLARE @SQLString NVARCHAR(MAX);
DECLARE @driver NVARCHAR(128)
SET @driver = '{MySQL ODBC 5.3 ANSI Driver}'

DECLARE @query NVARCHAR(MAX)
SET @query =''''+@sqlquery+''''
DECLARE @dbid  VARCHAR(10) = 0
DECLARE @Connection_string NVARCHAR(MAX)
DECLARE @tablequery NVARCHAR(MAX);

SET @connection_string= (SELECT connectionstring from ##mappingtable WHERE dbid = @dbid)
SET @tablequery =N'    
                       IF OBJECT_ID(''tempdb.dbo.##temptable'', ''U'') IS NOT NULL
                       DROP TABLE ##temptable; 
                          SELECT TOP 0 * INTO ##temptable FROM OPENROWSET(''MSDASQL'',
                                        ''Driver='+@driver+';'+@connection_string+''','+@query+')'


EXECUTE sp_executesql @tablequery;
WHILE (@dbid <=10)
BEGIN
IF  @dbid >=0
SET @connection_string= (SELECT connectionstring from ##mappingtable WHERE dbid = @dbid)
BEGIN
   SET @SQLString = N'INSERT INTO ##temptable
                   SELECT * FROM OPENROWSET(''MSDASQL'',
                                        ''Driver='+@driver+';'+@connection_string+''','+@query+')' 
END           

--PRINT @SQLString
EXECUTE sp_executesql @SQLString;

SET @dbid =@dbid +1

END
SELECT * FROM ##temptable;

END TRY
BEGIN CATCH
        PRINT ERROR_MESSAGE();
        PRINT 'Please fix the errors';             
END CATCH
--Query end

END

Here is the sample code I am working with it,works fine now.Please let me know for further questions.

This reply was modified 3 years, 8 months ago by sathwik.em91.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Thank you for posting your code. Now, what errors are you getting when you run this code? I can't actually run the code as I don't have servers to run this against.