CODEWITHSUNDEEP
sql-server-2008
issam ozeir
issam ozeir

Reputation: 23

DYNAMIC UNION TABLES

I am trying to Create a Dynamic Table where I don't Have to update the Table name each and every month. since i am working with the same query (no changes but the table name in the form (dbo.tablename_201705) to (dbo.tablename_201706) and so on. so i created this 

DECLARE @TABLE_NAME VARCHAR(20) 
SET @TABLE_NAME ='dbo.GTI_'+left (convert(varchar,dateadd(mm,-3, getdate()-7), 112) ,6)

DECLARE @GTI_SQL_REPORT VARCHAR(255) 
SET @GTI_SQL_REPORT = 'select*from '+@TABLE_NAME.

And from here I am lost I don't know the next step and here below is my query

Select 
   [CountryID], 
   COUNT([Document name]) as [inputcount], 
   MONTH([Creation Date]) AS [Creation Month], 
   Year([Creation Date]) as [Creation Year]
from 
   dbo.GTI_201705 
WHERE  
   [doc_type_product_type] not in ('CUST')
   and [Document_Source] in ('FAX','Original')  
group by 
   [CountryID],
   MONTH([Creation Date]) ,
   Year([Creation Date]) 
order by 
   [CountryID];

so the idea is to create a dynamic table so that this query will run at the beginning of each month. thank you in advance

Upvotes: 2

Views: 49

Answers (1)

S3S
S3S

Reputation: 25112

You were close... You just need to set the @GTI_SQL_REPORT to varchar(max) and then set the query to it. varchar(255) wouldn't be enough to hold your dynamic string. Notice the print(@GTI_SQL_REPORT) at the bottm. It's wise to do this first, so you will see what SQL you are executing. Once you are satisfied... then just comment it out and uncomment out the EXEC command.

DECLARE @TABLE_NAME VARCHAR(20) 
SET @TABLE_NAME ='dbo.GTI_'+left (convert(varchar,dateadd(mm,-3, getdate()-7), 112) ,6)

DECLARE @GTI_SQL_REPORT VARCHAR(max) 
SET @GTI_SQL_REPORT = 
    'Select 
       [CountryID], 
       COUNT([Document name]) as [inputcount], 
       MONTH([Creation Date]) AS [Creation Month], 
       Year([Creation Date]) as [Creation Year]
    from ' + @TABLE_NAME + '
    WHERE  
       [doc_type_product_type] not in (''CUST'')
       and [Document_Source] in (''FAX'',''Original'')  
    group by 
       [CountryID],
       MONTH([Creation Date]) ,
       Year([Creation Date]) 
    order by 
   [CountryID]; '

print(@GTI_SQL_REPORT)
--exec(@GTI_SQL_REPORT)

Upvotes: 1

Related Questions