Dynamic SQL with Temp Tables

Question

Post reply

Dynamic SQL with Temp Tables

ntran777

SSC Eights!

Points: 981
More actions
January 25, 2011 at 3:13 pm

#230811

Hello all,
I'm having a bit of a problem here. I need a proc that can make a variable number of temporary tables on the fly. It would have to take an input that tells it how many to make and then create them using a naming convention such as #temp1, #temp2, #temp3, etc.
Obviously my problem is that I would have to use dynamic SQL in which case the temp tables would go out of scope after the execute statement.
I would use global tables but I need to be able to access this proc concurrently from other sessions.
Any ideas on my options here?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

How about using a new database and creating uniquely named tables - perhaps include the SPID in the name

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Have you considered doing something like this:

SELECT EntryId, Entry, Description

INTO #T

FROM logentries

The INTO statement will create the temporary table.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ntran777 SSC Eights! Points: 981 More actions · Answer 3

Ron, I don't think this would work for my situation b/c the table would still be created in the scope of the exec statement if I were to use dynamic sql to generate names.

I need something that would let me either create the tables in the same scope as my stored procedure or some other way to create table names on the fly.

WayneS SSC Guru Points: 95460 More actions · Answer 4

mister.magoo (1/25/2011)
How about using a new database and creating uniquely named tables - perhaps include the SPID in the name

This sounds like an excellent idea. I'm not sure that a new database would be necessary.

Edit: A permanent table would allow the procedure to create the table dynamically, then use it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ntran777 SSC Eights! Points: 981 More actions · Answer 5

Okay, it seems a permanent table could be used with session IDs appended.

What are the I/O implications with this though? This will be put on a server with some heavy traffic from multiple clients. I'm afraid that the performance may take a big hit using permanent tables.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6

ntran777 (1/25/2011)
I need a proc that can make a variable number of temporary tables on the fly.

I would be interested in knowing why you need to do this. It may be that there are better ways around the problem.

eg If the tables have the same structure you could create one temporary table with the addition of a SetNumber column.

ntran777 SSC Eights! Points: 981 More actions · Answer 7

I'm creating a table for reporting that has too many columns in it to return as one. The rows go over SQL's 8060b limit. To solve this, I'm gathering the data into smaller tables and joining them together for the application to use. I don't know how many tables will need to be created. It depends on how many columns the particular customer has defined.

lnoland SSCommitted Points: 1753 More actions · Answer 8

ntran777 (1/26/2011)
I'm creating a table for reporting that has too many columns in it to return as one. The rows go over SQL's 8060b limit. To solve this, I'm gathering the data into smaller tables and joining them together for the application to use. I don't know how many tables will need to be created. It depends on how many columns the particular customer has defined.

How about using dynamic SQL to create the stored procedure which creates and uses the temp tables?

- Les