Query a table that changes name based on year and month

Question

Our agency has more than 200 fire trucks and 50 ambulances. Each of these apparatus has a gps on board that registers data into a SQL Server 2012 database. Each month it is millions of records of data. Each month the name of the table changes. For example; This month (16 December 2015), the name of the table is GPS.dbo.History201512 and it will hold gps data only for the month of December in 2015. Next month (January 2016), the name shall be GPS.dbo.History201601 and it will maintain data for january 2016. So the pattern is GPS.dbo.HistoryYYYYMM. A simple query would be:

SELECT DateTimeStamp, Longitude, Latitude, Apparatus
FROM GPS.dbo.History201512
WHERE Apparatus = 'Ambulance32'

So this is a moving target. The table changes names on a predictable pattern. What can I do so I can program this query to query the current month. If today's date is 4 July 2016 then query the table GPS.dbo.History201607. If today's date is 9 November 2017 then query the table GPS.dbo.History201711.

How do you program the query such that it picks up on the system calendar date and then queries the appropriate monthly table?

I have to wonder why one table was not used here. One column in one table can hold this information. — Peter_James, Dec 16 '15 at 14:18
See http://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query — Peter_James, Dec 16 '15 at 14:19
why not use only one table and an index on the date or a partitioned table? — Julien Vavasseur, Dec 16 '15 at 14:19

score 1 · Answer 1 · answered Dec 16 '15 at 14:25

1

A slight improvement to Peter_James answer:

SET @TableName = 'GPS.dbo.History' + CONVERT(CHAR(6), GETDATE(), 112)
SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID'
EXEC @SQLQuery

answered Dec 16 '15 at 14:25

mortb

9,361
3
26
44

Peter_James · Answer 2 · 2015-12-16T14:50:03.747

0

Creating a stored procedure passing in the YYYYMM as a parameter would be your best bet.

Alternatively:

SET @TableName = 'GPS.dbo.History' + '201512'
SET @SQLQuery = 'SELECT * FROM ' + @TableName
EXEC @SQLQuery

edited Dec 16 '15 at 14:50

answered Dec 16 '15 at 14:21

Peter_James

647
4
14

score 0 · Answer 3 · answered Dec 16 '15 at 14:37

You could create a partitioned view to link them up:

CREATE VIEW GPS.dbo.History
AS
SELECT COL1,COL2 FROM GPS.dbo.History201510
UNION ALL
SELECT COL1,COL2 FROM GPS.dbo.History201511
UNION ALL
SELECT COL1,COL2 FROM GPS.dbo.History201512

This needs to be maintained manually

If you put appropriate check constraints in the table, and use an appropriate where clause, the optimiser will be smart enough to only look in the correct table

https://technet.microsoft.com/en-au/library/ms190019(v=sql.105).aspx

You should analyse the types of queries you get. You might be able to do other things like utilise a cube

Query a table that changes name based on year and month

3 Answers3