I need to merge (
UNION) data from annual tables into one large table. I am confused about how dynamic SQL (or else) should be used for this. Below is an updated version of my question on StackOverflow, probably better suited here.
The data is in MonetDB, which follows SQL 2003. I am not sure they support partitioning though, and I would rather merge my tables in any case.
The discussion on
SELECT * FROM sales + @yymm in The Curse and Blessings of Dynamic SQL does not mention a solution in the end, even though mine would sound like a legitimate use case and "often asked in SQL mailing lists and forums."
I received some guidance about the relevant pieces from a MonetDB expert in a comment below his answer, but without the loop over years, which I still need.
Think of my data having tables like
CIVIL_2012. These usually follow the same schema, but have no
year column. I would want to have a single
CIVIL table, with a
year columns as well.
I want to avoid spelling out 1969, 1970 etc. in my code manually, that is unwieldy and error-prone.
There are also tables where the schema do change from year to year. Is it possible to merge these tables as well? Sure, some of the columns would have sparse records, missing for many years.
Some very tentative pseudocode on this:
USE dbfarm DECLARE @i INT SET @i = 1990 SELECT name FROM tables WHERE name LIKE 'data_@i'; WHILE @i < 2013 DO ALTER TABLE data_@i ADD COLUMN "year" INTEGER; UPDATE data_@i SET "year" = @i; SET @i = @i +1 END WHILE CREATE TABLE data AS SELECT * FROM data_1990 UNION ALL SELECT * FROM data_1991 UNION ALL [...] WITH DATA;