Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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_1969, CIVIL_1970CIVIL_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;
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.