Temporary tables in a WHILE LOOP

Question

I have a question concerning a dynamic creation of temporary tables.

I need a while loop in which temporary tables are created in every cycle differing in their name using the variable @i. So I specified the variables @CreateTableVerbGES and @ShowTableVerbGES with the code as strings and want to execute them in the loop.

Here is my code:

DECLARE @i int = 1
DECLARE @CreateTableVerbGES nvarchar(max)
DECLARE @ShowTableVerbGES nvarchar (max)

WHILE @i<4

BEGIN

SET @CreateTableVerbGES = 'CREATE TABLE #UnterBaugruppe'+CAST((@i) as nvarchar(max))+' ([IDTBG] [int] identity(1,1),[IDGES] [int] NULL,[Baugruppe1] [nvarchar](max) NULL,[IDBG1] [int] NULL,[Bauteil1] [int] NULL,[Baugruppe2] [nvarchar](max) NULL,[IDBG2] [int] NULL,[Bauteil2] [int] NULL,[Baugruppe3] [nvarchar](max) NULL,[IDBG3] [int] NULL,[Bauteil3] [int] NULL)'

SET @ShowTableVerbGES = 'SELECT * FROM #UnterBaugruppe'+CAST((@i) as nvarchar(max))

EXEC (@CreateTableVerbGES)
EXEC (@ShowTableVerbGES)

SET @i += 1

END

The problem is that it seems like the first cycle of the loop is running, because the first table #UnterBaugruppe1 is shown. But for the next two cycles there is a warning that the tables #UnterBaugruppe2 and #UnterBaugruppe3 are invalid and don't exist.

Does anybody have an idea why it isn't working?

I really need the tables in a loop because I want to insert different information in the loop as well dependent on @i.

I don't understand anything, give table structure, give table sample data, and tell us what you exactly want to achieve. — user1735921, Nov 19 '15 at 09:18
Give it a shot to go with 'CREATE TABLE ##UnterBaugruppe' and let me know if its working ... ofc don't forget to 'SELECT * FROM ##UnterBaugruppe' — Veljko89, Nov 19 '15 at 09:23
The table structure and everything conerning the table is defined in 'CreateTableVerbGES'. This varaible is a string containing the complete code for creating a table. Since I want to create the tables in a loop with the 'i' as a variable table name, I safed the code in a variable so that I just have to execute it in the loop. — T.R., Nov 19 '15 at 09:25
I think that `SET @i += 1` is not valid in tsql. what server version do you have? — Saic Siquot, Nov 19 '15 at 09:26
Ok, I tried it with ##UnterBaugruppe! That works. Thanks! But if I want to run the loop a second time it always tells me that the table already exists. Is there a way to first proof if the table exists and if it does to delete it? — T.R., Nov 19 '15 at 09:27
Hi Luis, the @i += 1 is working. I already tried that in another loop! — T.R., Nov 19 '15 at 09:30
IF OBJECT_ID('TEMPDB.dbo.##UnterBaugruppe'+CAST((@i) as nvarchar(max))) IS NOT NULL DROP TABLE ##UnterBaugruppe'+CAST((@i) as nvarchar(max)) — Veljko89, Nov 19 '15 at 09:30
http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server — Werner Henze, Nov 19 '15 at 09:45
A table namend with two hashtags ## is a global temporary table. So it can be used from every session. And if multiple sessions run the same TSQL-Statement it might not be doing what it should be. — CPMunich, Nov 19 '15 at 10:04

score 0 · Answer 1 · answered Nov 19 '15 at 10:35

I just concatenated the dynamic sql of two variable into single using new line character and executed and it perfectly worked for me!

DECLARE @i int = 1
DECLARE @CreateTableVerbGES nvarchar(max)
DECLARE @ShowTableVerbGES nvarchar (max)

WHILE @i<4

BEGIN

SET @CreateTableVerbGES = 'CREATE TABLE #UnterBaugruppe'+CAST((@i) as nvarchar(max))+' ([IDTBG] [int] identity(1,1),[IDGES] [int] NULL,[Baugruppe1] [nvarchar](max) NULL,[IDBG1] [int] NULL,[Bauteil1] [int] NULL,[Baugruppe2] [nvarchar](max) NULL,[IDBG2] [int] NULL,[Bauteil2] [int] NULL,[Baugruppe3] [nvarchar](max) NULL,[IDBG3] [int] NULL,[Bauteil3] [int] NULL)'
+ CHar(10) + Char(13) + 'SELECT * FROM #UnterBaugruppe'+CAST((@i) as nvarchar(max)) + CHar(10) + Char(13) 

Print @CreateTableVerbGES
EXEC (@CreateTableVerbGES)
--EXEC (@ShowTableVerbGES)

SET @i += 1

END

Good idea! I will try that as well! – T.R. Nov 19 '15 at 14:18 — T.R., Nov 19 '15 at 14:18

Temporary tables in a WHILE LOOP

1 Answers1