Wednesday, February 13, 2013 4:20 PM
How can I change the database dynamically in the query based on the date?
We have following 13 database with same table in each database.
Current- Month to date data
M01-End of month data for Last Jan
M02-End of month data for Last Feb
All these data base have same table1 ( Account int , Status ( value can be either ‘Y’ or ‘N’)
Each day I need to get all the account from current database and get status code for last 8 months
Account 8 month trend ( as string for each month)
123 YYNYNYYY- this is picked up from table1 from different database.
To get 8 month trend, let’s say we want to run the report today
It should pick the status code from Current,M01, M12, ,M11, M10, M09,M08.M07
If we run report in next month(march) it should pick the status code from Current , M02,M01, M12,M11,M10,M09,M08
Wednesday, February 13, 2013 4:32 PMModerator
You can develop a stored procedure for the entire task.
To loop through the database, you can use dynamic SQL:
You can schedule the sproc as a SQL Server agent job:
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Friday, February 15, 2013 3:19 AM
Wednesday, February 13, 2013 4:38 PM
You can easily get the month out of a date, and you can also add or subtract months to that date. you can then append an M to your results, and use it in the form (between a while loop):
set @var= 'insert into table (col1,col2)
select col1,col2 from ['''+@dbname+'''].[schema].[tablename]'
But looking at your requirements, it seems to me that you need to re-check your requirements and implementation. Are you going to create a new db for each month? if so, what happens after 1 year?
Wednesday, February 13, 2013 4:43 PMThanks its very helpful.
Wednesday, February 13, 2013 4:43 PMthanks-any time there is just 13 database- for current month and last 12 month.
Wednesday, February 13, 2013 10:00 PM
This poor man's partitioning is bound to incur some pain.
The best I think is to set up partitioned views over the table. I have an introduction here:
You would have to use dynamic SQL to set up the views, and maybe also add the constraints, but your queries would have to be polluted.
How big are these databases?
Erland Sommarskog, SQL Server MVP, email@example.com