Sql-server – Refactoring sql for tables with similar columns but different table names

dynamic-sqlquery-refactorsql serversql-server-2005union

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.

Much appreciated.

Best Answer

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter 
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0 
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0 
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0 
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0 
BREAK
END
RETURN
END


GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'


DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function 
FROM    dbo.fnSplit(@varyears, ',')


EXEC ( @combinedString )
Related Question