Creating an Dynamic Tables in MS-SQL (feasibility)

Question

Is there a way to create an array of tables (or dynamical created tables) based on a dynamic value? For example I know that in C++ you can use pointers and dynamic memory but i need to do something like this in SQL.

A user specifies they want to see X years of data for each year we need see 15+ columns that can be built into a temp table (identical for each year).

user selects 5 years worth of data and current year as 2018, so I will want to create dynamically a local table for 2018, 2017, 2016, 2015 and 2014. Each table will track customer data, sales etc. Same for each table.

if the above is not possible could we limit the user to only pick the year and dynamically always stick to 5 years for example. User specifies they want to start with 2017 and we will by default build for them 2017, 2016 ... 2013.

i can go ahead and say create table #year1, #year2, #year3 etc. But that requires a lot of coding (mostly copying and pasting in advance). Is there a way to avoid this -- to say create table #years[] or a pointer? and then dynamically build some kind of array of #years table.

What we will end up doing is going through the exact same query/logic pulling the exact same data sets year by year. Then at the end comparing who/falls into which year and how. Bottom line is to avoid copying and pasting the same declare/select/insert statements 5 or more times? (and if at all possible to avoid cursors/loops)

I tried coding something like this

DECLARE @season_counter int = 5

    DECLARE @cnt int = 0
    Declare @cnt_v varchar(1)  =  CONVERT(varchar(10), @cnt)

    DECLARE @SQL NVARCHAR(max) = ''

     WHILE @cnt <= @season_counter
     BEGIN
        select   @SQL =  @SQL + ' create table #year' + @cnt_v
         +  ' (customer_no int, order_no int, perf_no int, due_amt money, paid_amt money, status int ) ' 

        exec sp_executesql @SQL
        print @SQL 

        set @cnt = @cnt + 1
        set @cnt_v = CONVERT(varchar(10), @cnt)
        set @sql = ''


    END

select * from #year1
select * from #year2

The above generated errors

Msg 208, Level 16, State 0, Line 81
Invalid object name '#year1'.

I cant use global variables and i sure as hell don't want to declare Create statements for 20 tables (as some max value). I want it to be dynamic and on the fly. Please advise.

@MitchWheat this is too small a project for cubes (for us anyways). — Elizabeth, Oct 18 '18 at 13:44

Russell Fox · Answer 1 · 2018-10-18T15:13:26.180

0

I don't see any way to do this without using some sort of loop and dynamic SQL. [Added:] Your loop is running into a known limitation of SQL Server: you cannot create a temp table via dynamic SQL. If the temp table is created first, you can then refer to it in dynamic SQL, but that defeats the purpose. You can, however, create a table via the SELECT INTO functionality. This creates an actual table, but you can drop and re-create them in your code.

Personally, I also like to create a new schema for such things - generally "x" - so that in Management Studio they only show up at the bottom, after all the "dbo" schema tables. You only create a schema once, so don't include that part in your loop:

CREATE SCHEMA x;
GO

DECLARE @SQLCmd NVARCHAR(MAX)
--Loopify this part:
SET @SQLCmd = 
'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Temp1'')
    DROP TABLE Temp1;

SELECT *
INTO x.Temp1
FROM YourDB.dbo.YourTable
WHERE foo = ''bar'';'

--PRINT @SQLCmd
EXEC(@SQLCmd);

edited Oct 18 '18 at 15:13

answered Oct 18 '18 at 00:08

Russell Fox

5,273
1
24
28

How would i use it to create X amount of tempt tables for example. I get writing scrips that are passed as parameters and executed as part of strings. but what do i do to create #table1, #table2 etc. I need create/populate tables. – Elizabeth Oct 18 '18 at 13:50
That is a good idea, test to see if the temp table exists -- if yes drop it. then create it from scratch. I will test this. What will happen though if multiple people try to run it. one gets to drop the table while the other is trying to populate it. – Elizabeth Oct 18 '18 at 15:47
Yeah, that would be a problem. You could add a timestamp to the name of the table or something like that, but you should have some sort of cleanup process, like an agent that runs at midnight and deletes any table that starts with "temp". – Russell Fox Oct 22 '18 at 17:16

score 0 · Answer 2 · answered Oct 18 '18 at 15:51

Definitely, dynamic SQL can be your friend here: For example to create pivot like you required you can use script like below:

GO
DECLARE @startYear AS INT = 2000
DECLARE @endYear AS INT =2100
--Magic insert
;
WITH L0
AS ( SELECT c
     FROM   (   SELECT 1
                UNION ALL
                SELECT 1 ) AS D(c) ) , -- 2^1
     L1
AS ( SELECT NULL AS c
     FROM   L0 AS A
            CROSS JOIN L0 AS B ) ,       -- 2^2
     L2
AS ( SELECT NULL AS c
     FROM   L1 AS A
            CROSS JOIN L1 AS B ) ,       -- 2^4
     L3
AS ( SELECT NULL AS c
     FROM   L2 AS A
            CROSS JOIN L2 AS B ) ,       -- 2^8
     L4
AS ( SELECT NULL AS c
     FROM   L3 AS A
            CROSS JOIN L3 AS B ) ,       -- 2^16
     L5
AS ( SELECT NULL AS c
     FROM   L4 AS A
            CROSS JOIN L4 AS B ) ,       -- 2^32
     Nums
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS id
     FROM   L5 ) ,
     years
AS ( SELECT id AS [year]
     FROM   Nums
     WHERE  id >= @startYear AND id<=@endYear )

SELECT [year] INTO #years
FROM   years


DECLARE @columns VARCHAR(MAX)='' 
DECLARE @curentYear int = @startYear;
WHILE @curentYear <= (SELECT MAX([year]) from #years)
BEGIN
    SET @columns+= '['+(SELECT Cast([year] as VARCHAR(4)) FROM #years WHERE [year] = @curentYear)+'],'
    SET @curentYear += 1;
END

SET @columns = SUBSTRING(@columns,1,LEN(@columns)-1)

DECLARE @SQL NVARCHAR(max)=
'SELECT * FROM #years PIVOT(MAX([year]) FOR [year] IN ('+   @columns+')) AS piv'

EXEC sys.sp_executesql @SQL

Creating an Dynamic Tables in MS-SQL (feasibility)

2 Answers2