question

chelsonkennedy avatar image
chelsonkennedy asked

Dynamic Query

I have historic tables in my database created with dates attached to the name of the table. For example DatabaseName; [Honda] TableNames Chels_20140725 Chels_20140724 Chels_20140723 Chels_20140722 Chels_20140721 Chels_20140718 Chels_20140717 Chels_20140716 Now I want a dynamic query that will select some fileds let's say A, B, C, D FROM the Table that is lastweek from today. SO For today being Chels_20140725, I want the table Chels_20140718. For Monday being Chels_20140728 the table will be Chels_20140721. How do I write this query? Any help will be highly appreciate. Thanks
tables
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Have you considered a partitioned view? You could let SQL figure out which table to query instead of coding that up yourself. http://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx This sounds like the behavior you are after: "The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables. Therefore, it limits its search to those tables"
0 Likes 0 ·
virtualjosh avatar image
virtualjosh answered
I'll just point you in the right direction.... Read this document: [ http://www.sommarskog.se/dynamic_sql.html#queryplans][1] [1]: http://www.sommarskog.se/dynamic_sql.html#queryplans
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark avatar image
@SQLShark answered
Hi, this doesn't look like a great design. Personally I would just have one archive table and date stamp the archive rows. Then you will only hit 1 table and 1 index (if you index). If you are stuck with this design here is my solution (it could be improved). You need a tally table. For the purpose of the query I created a temp one. CREATE TABLE #Chels_20140725 ( Col1 VARCHAR(50) ) CREATE TABLE #Chels_20140724 ( Col1 VARCHAR(50) ) CREATE TABLE #Chels_20140723 ( Col1 VARCHAR(50) ) CREATE TABLE #Chels_20140722 ( Col1 VARCHAR(50) ) CREATE TABLE #Chels_20140721 ( Col1 VARCHAR(50) ) CREATE TABLE #Chels_20140720 ( Col1 VARCHAR(50) ) CREATE TABLE #Chels_20140719 ( Col1 VARCHAR(50) ) INSERT INTO #Chels_20140725 ( Col1 ) VALUES ( '20140725' ) INSERT INTO #Chels_20140724 ( Col1 ) VALUES ( '20140724' ) INSERT INTO #Chels_20140723 ( Col1 ) VALUES ( '20140723' ) INSERT INTO #Chels_20140722 ( Col1 ) VALUES ( '20140722' ) INSERT INTO #Chels_20140721 ( Col1 ) VALUES ( '20140721' ) INSERT INTO #Chels_20140720 ( Col1 ) VALUES ( '20140720' ) INSERT INTO #Chels_20140719 ( Col1 ) VALUES ( '20140719' ); DECLARE @Tally TABLE ( TallyDate DATE ); WITH cteCounter AS ( SELECT 0 AS N UNION ALL SELECT N + 1 FROM cteCounter WHERE N < 99 ) INSERT INTO @Tally SELECT StartOfMonth = DATEADD(day, -N, '08-01-2014') FROM cteCounter; DECLARE @FileDate DATE DECLARE @DynamicSQL VARCHAR(MAX) = '' DECLARE TallyCursor CURSOR FOR SELECT * FROM @Tally WHERE TallyDate > DATEADD(DAY, -7, '2014-07-25') AND TallyDate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.