I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except that the table names are different. For example:
customerTransaction2011_Tbl |ID | T.ID | Amount | |---|------|--------| CustomerTransaction2012_Tbl |ID | T.ID | Amount | |---|------|--------| CustomerTransaction2013_Tbl |ID | T.ID | Amount | |---|------|--------|
The SQL script I have to re-factor is below:
Select ID, Sum(Amount), '2011' as [Year] into #Tbl_threeYear From CustomerTransaction2011_Tbl union all Select ID, Sum(Amount), '2012' as [Year] From CustomerTransaction2012_Tbl union all Select ID, Sum(Amount), '2013' as [Year] From CustomerTransaction2013_Tbl;
To re-factor this, I had thought of using
Dynamic SQL within a
stored procedure, so that I could pass the years (2011, 2012, 2013) as parameters to the stored procedure, which would append these to the Dynamic SQL string.
But turns out I can't be given permission to create a stored procedure.
So 2 questions, please:
If I simply make use of
Dynamic SQL like so
declare @sqlmain as varchar(500); declare @tblPart as varchar(100); set @tblPart = '2011'; set @sqlmain = 'select ID, sum(Amount) from customerTransaction' + @tblPart + '_Tbl;'
How could I perform a union over the results from
exec(sqlmain) while simply changing the @tblPart variable - must be possible somehow?
Also, would you rather another way of re-factoring such a script? Since this is my first time re-factoring, I would more than welcome any suggestions/criticism. Perhaps anybody could suggest how to re-factor in basic SQL?
This is all being done in SQL Server 2005.