Clash Royale CLAN TAG#URR8PPP

Loop through CTE results and run a query for each

I'm trying to create a SQL query/procedure that would loop through records the CTE brings back.

The CTE would return table names of tables that have to have the queries executed to.

Example CTE Results:

op2018al
op290717al


and more...

The query that would need to be run for the two tables from CTE would be:

SELECT COUNT(*) AS Records_missing
FROM scheme./** tables form the CTE **/ a WITH (NOLOCK)
WHERE NOT EXISTS (
SELECT *
FROM table_a b WITH (NOLOCK)
WHERE a.COLUMN = b.COLUMN
)


I believe that this would have to be a stored procedure including a CURSOR function.

1 Answer
1

since you need to use Dynamic SQL, you can form the query for individual table and UNION ALL the result

UNION ALL

declare @sql nvarchar(max)

; with cte as
(
< your cte query>
)
select @sql = isnull(@sql + char(13) + 'UNION ALL' + char(13), '')
+ 'SELECT tbl_name = ''' + tbl_name + ''', COUNT(*) AS Records_missing' + char(13)
+ 'FROM ' + quotename(tbl_name) + ' AS a' + char(13)
+ 'WHERE NOT EXISTS (' + char(13)
+ 'SELECT *' + char(13)
+ 'FROM table_a b' + char(13)
+ 'WHERE a.COLUMN = b.COLUMN)' + char(13)
from cte

print @sql
exec sp_executesql @sql


Union All

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.