dynamic sql: merge partitioned tables (annual tables into a single table of all years)

Question

I need to merge the data from annual tables into one large table. I am confused about how dynamic SQL (or else) should be used for this.

All this is in MonetDB, which follows the SQL 2008 standard, I think. 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.

I received some guidance about the relevant pieces from a MonetDB expert in a comment below his answer over on DBA, but without the loop over years, which I still need.

Think of my data having tables like CIVIL_1969, CIVIL_1970 … 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.

By the way, there are tables where the schema do change from year to year (e.g. tax forms collected different records for different years). 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;

Lennart Lennart · Accepted Answer · 2014-05-17T21:26:08

I don't know monetdb, but I assume they support views. One approach is to create a view that describes the merging of data, and then use that to insert into the table. I.e.:

create view v as (
    select 1969 as year, c1, c2, ..., cn, cast(null as ...) -- col introduced in 1973
         , cast(null as ...) -- col introduced in 1987
    union all 
    ...
    select 1973 as year, c1, c2, ..., cn, cn_1, , cast(null as ...) -- introduced in 1987   
    union all
    ...
)

insert into data (...) select ... from v;

dynamic sql: merge partitioned tables (annual tables into a single table of all years)

1 Answers