CODEWITHSUNDEEP
sqlsql-serverloopsdynamic-sql
not_ur_avg_cookie
not_ur_avg_cookie

Reputation: 333

Loop query through multiple tables

I would like to the same query which should almost never return any results through multiple tables. The table name is the same except with the addition of the year. So my query looks like this:

select p.productID, po.name, p.price from prices_2001  p
join products po on p.productID = po.id
where price < 0

and I would like to loop through all the years from 2001 to 2020. So it should look like this:

select p.productID, po.name, p.price from prices_2001  p
join products po on p.productID = po.id
where price < 0

select p.productID, po.name, p.price from prices_2002  p
join products po on p.productID = po.id
where price < 0

select p.productID, po.name, p.price from prices_2003  p    --Looping trough all the tables until 2020
join products po on p.productID = po.id
where price < 0

If there are results they can be stored in a temp table. I thought of creating a loop with the table names or creating dynamic sql query. What is the best practice and how can I use this query for all the tables just by executing it once?

Thank you!

Upvotes: 0

Views: 2020

Answers (2)

Thom A
Thom A

Reputation: 96016

To reiterate my comment:

This sounds like a denormalisation problem, really you should have 1 table with a column for the year.

Really, you should be fixing your design.

Anyway, onto the problem: You can do this with dynamic SQL, yes, but you'd be better off fixing the design. This however, create and executes the statement dynamically. i've used FOR XML PATH due to a lack of version tag:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);


SET @SQL = STUFF((SELECT @CRLF + N'UNION ALL' + @CRLF +
                         N'SELECT p.productID, po.name, p.price' + @CRLF +
                         N'FROM ' + QUOTENAME(t.[name]) + N'p' + @CRLF +
                         N'     JOIN product po ON p.productID = po.id' + @CRLF +
                         N'WHERE p.price < 0'
                  FROM sys.tables t
                  WHERE t.[name] LIKE N'prices[_]20[0-9][0-9]'
                  ORDER BY t.[name]
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,13,N'') + N';';

--PRINT @SQL; --Your debugging friend

EXEC sys.sp_executesql @SQL;

Upvotes: 2

JNevill
JNevill

Reputation: 50308

This is difficult and costly (looping a SQL statement!) because your schema is bad. You should instead have a single table prices that is identical to your existing tables, but has one extra column called year. Then joining becomes simple and you don't have to create new tables just because the date changed.

Instead though you can UNION your tables together and get the same thing. Something like:

SELECT p.productID, po.name, p.price, po.year
FROM products p
     INNER JOIN 
         (
              SELECT id, name, 2001 as year FROM prices_2001
              UNION ALL SELECT id, name, 2002 FROM prices_2002
              UNION ALL SELECT id, name, 2003 FROM prices_2003
              UNION ALL ... <continue until you have all year tables accounted for>
         ) po
         ON p.productID = po.id
WHERE po.price < 0;

Consider writing the results of that UNION subquery to a new table though and just using that from now on.

Upvotes: 1

Related Questions