[dba-SQLServer] Looping through queries over and over

[David McAfee]

Ok, I was able to do this in SSMS with hardcoded values, but I'd like to
make it dynamic.

Maybe a better question would be:

Given a select query or temp table of these batchnames:
Month Begin 20141202
Month Begin 20150106
Month Begin 20150203
Month Begin 20150302
Month Begin 20150403
Month Begin 20150501

How can I dynamically come up with a loop or fill a temp table to look like
this:
Step tableABatch1       tableABatch2  tableTBatch1       tableTBatch2
1 Month Begin 20141202 Month Begin 20150106 Month Begin 20141202 Month
Begin 20150203
2 NULL NULL Month Begin 20141202 Month Begin 20150302
3 NULL NULL Month Begin 20141202 Month Begin 20150403
4 NULL NULL Month Begin 20141202 Month Begin 20150501
5 Month Begin 20150106 Month Begin 20150203 Month Begin 20150106 Month
Begin 20150302
6 NULL NULL Month Begin 20150106 Month Begin 20150403
7 NULL NULL Month Begin 20150106 Month Begin 20150501
8 Month Begin 20150203 Month Begin 20150302 Month Begin 20150203 Month
Begin 20150403
9 NULL NULL Month Begin 20150203 Month Begin 20150501
10 Month Begin 20150302 Month Begin 20150403 Month Begin 20150302 Month
Begin 20150501
11 Month Begin 20150403 Month Begin 20150501 NULL NULL


The reason that I ask is because this is what I ended up doing (manually):
for steps 1-4, above
The big delete statements below are identical

DECLARE @TempTableA TABLE(fields....) --Cumulative table
DECLARE @TempTableT TABLE(fields....) --temporary temp table :)

INSERT INTO @TempTableA EXEC stpLossOfMediCalDetail 'Month Begin
20141202','Month Begin 20150106'  --Step 1 above
INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
'Month Begin 20150203' --Step 1 above

DELETE @TempTableA
 FROM @TempTableA B
LEFT JOIN
(SELECT A.*
FROM @TempTableA A
INNER JOIN @TempTableT T
ON A.EligNumber =T.EligNumber
AND A.AccountNumb = T.AccountNumb
AND A.FirstDate = T.FirstDate) C
ON B.EligNumber =C.EligNumber
AND B.AccountNumb = C.AccountNumb
AND B.FirstDate = C.FirstDate
WHERE C.AccountNumb IS NULL
AND C.FirstDate IS NULL
AND C.EligNumber IS NULL

DELETE FROM  @TempTableT

INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
'Month Begin 20150302' --Step2

DELETE @TempTableA
 FROM @TempTableA B
LEFT JOIN
(SELECT A.*
FROM @TempTableA A
INNER JOIN @TempTableT T
ON A.EligNumber =T.EligNumber
AND A.AccountNumb = T.AccountNumb
AND A.FirstDate = T.FirstDate) C
ON B.EligNumber =C.EligNumber
AND B.AccountNumb = C.AccountNumb
AND B.FirstDate = C.FirstDate
WHERE C.AccountNumb IS NULL
AND C.FirstDate IS NULL
AND C.EligNumber IS NULL

DELETE FROM  @TempTableT

INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
'Month Begin 20150403' --step3

DELETE @TempTableA
 FROM @TempTableA B
LEFT JOIN
(SELECT A.*
FROM @TempTableA A
INNER JOIN @TempTableT T
ON A.EligNumber =T.EligNumber
AND A.AccountNumb = T.AccountNumb
AND A.FirstDate = T.FirstDate) C
ON B.EligNumber =C.EligNumber
AND B.AccountNumb = C.AccountNumb
AND B.FirstDate = C.FirstDate
WHERE C.AccountNumb IS NULL
AND C.FirstDate IS NULL
AND C.EligNumber IS NULL
DELETE FROM  @TempTableT

INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
'Month Begin 20150501' --step4

DELETE @TempTableA
 FROM @TempTableA B
LEFT JOIN
(SELECT A.*
FROM @TempTableA A
INNER JOIN @TempTableT T
ON A.EligNumber =T.EligNumber
AND A.AccountNumb = T.AccountNumb
AND A.FirstDate = T.FirstDate) C
ON B.EligNumber =C.EligNumber
AND B.AccountNumb = C.AccountNumb
AND B.FirstDate = C.FirstDate
WHERE C.AccountNumb IS NULL
AND C.FirstDate IS NULL
AND C.EligNumber IS NULL

SET NOCOUNT OFF

SELECT * FROM @TempTableA --Final Select