0

Is there any way to do some type of loop through an array of strings in SQL? I am appending data to #T_POLICY, and I want to run through the queries with one database and the run through the same queries with a different database.

Ex.

use Staging__4SBI_STG_BG
go

WITH cteEnumerate AS
(
    SELECT *
           ,RN = ROW_NUMBER() OVER (PARTITION BY POLICY_ID ORDER BY LOADDATE DESC)
    FROM dbo.STG_POLICY
)
INSERT INTO #T_POLICY
SELECT SOURCE, AGENCY_D, POL_SEQ, POLICY_ID, POL_ENDNUMBER, PRODUCT_ID, 
COMPANY_ID
FROM cteEnumerate
WHERE RN = 1
ORDER BY POLICY_ID;

So the next one I would like use is use Staging__4SBI_STG_TB instead of BG, and have quite a few others to run through. Could I create a table with these names and run through them? Any help would be great.

Thanks

McNets
  • 10,352
  • 3
  • 32
  • 61
BGlove
  • 1
  • 1
  • Dynamic SQL within a stored procedure while loop or a cursor: http://stackoverflow.com/questions/18513986/how-to-write-a-foreach-in-sql-server Cursor would be for each database then dynamic SQL would be executed to change database. then your query... – xQbert May 10 '17 at 18:56
  • Thank you for your comment. It looks like a stored procedure is going to be the best option for me, although as a read only user I'm not sure if i have access to create stored procedures. – BGlove Nov 20 '17 at 03:04

1 Answers1

0

How do you load table dbo.STG_POLICY? You can add a column IsLatestPolicy BIT to the table. 

WITH cte AS (SELECT *
                           ,RN = ROW_NUMBER() OVER (PARTITION BY POLICY_ID ORDER BY LOADDATE DESC)
                      FROM dbo.STG_POLICY
)
UPDATE cte
SET IsLatestPolicy = CASE WHEN RN = 1 THEN 1 ELSE 0 END;

So every time you can select the original table

SELECT SOURCE
      , AGENCY_D
      , POL_SEQ
      , POLICY_ID
      , POL_ENDNUMBER
      , PRODUCT_ID
      , COMPANY_ID
FROM dbo.STG_POLICY
WHERE IsLatestPolicy = 1

Therefore no need to create another table just for initiating a loop.

Wendy
  • 640
  • 1
  • 4
  • 8
  • Thanks for your comment, I am just getting back into starting here so thank you for your answer. I unfortunately don't have access to load the data just view it, and I wish there was a latest policy info flag in the source data. I actually have to create temp tables and then I make some calculated fields and query off of the temp tables. – BGlove Nov 20 '17 at 03:01