Can you dynamically create a temp table?

Question

Can you dynamically create a temp table?

woody_rd

Ten Centuries

Points: 1012
More actions
February 13, 2014 at 9:01 am

#304410

Example: Run a query that finds the following:
User 1 has 5 quiz results
User 2 has 3 quiz results
User 3 has 1 quiz result
User 4 has 9 quiz results
I want to dynamically create a temp table 2ith 9 columns that I can use for lookups a later time. Is there a way to achieve this?
Many thanks for any suggestions!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Yes you can with dynamic code. But you might face the same issues when calling that table. Why wouldn't you want to use a normalized temp table?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

I would consider a static temp table and if you wish, look at creating a dynamic pivot query.

woody_rd Ten Centuries Points: 1012 More actions · Answer 3

Because my values can change each time I run it. Today there might be 9 quiz results, but tomorrow there might be 12. Just want to be able to dynamically built a table with 9 columns that I can use in my searches.

woody_rd Ten Centuries Points: 1012 More actions · Answer 4

I've never used a dynamic pivot. Can you give an example?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

To do a dynamic pivot, check the following article: http://www.sqlservercentral.com/articles/Crosstab/65048/

If you build your table something like this, it doesn't matter if you have 1, 3 or 200 results:

CREATE TABLE Quiz_Results(

quiz_id int,

result_no int,

result_value varchar(50) --int, decimal or whatever you need

)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Richard Warr SSCertifiable Points: 6966 More actions · Answer 6

If you did want to do it (and I agree that the normalised solution is better) you'd need to go with a Global Temp table rather than just a normal one.

For example, in this code:

DECLARE @sSQL NVARCHAR(MAX);

SET @sSQL =

'

CREATE TABLE #tmp (

Col1 INT,

Col2 INT);

INSERT INTO #tmp VALUES (1,21),(2,23),(3,35);

SELECT * FROM #tmp

'

EXEC sp_executesql @sSQL

SELECT * FROM #tmp

the select within the dynamic SQL works but the one outside doesn't. This is because the temp table is scoped within the dynamic code and not visible to anything else.

However, if you use a global temp:

DECLARE @sSQL NVARCHAR(MAX);

SET @sSQL =

'

CREATE TABLE ##tmp (

Col1 INT,

Col2 INT);

INSERT INTO ##tmp VALUES (1,21),(2,23),(3,35);

SELECT * FROM ##tmp

'

EXEC sp_executesql @sSQL

SELECT * FROM ##tmp

then both selects work fine.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Richard Warr (2/13/2014)
If you did want to do it (and I agree that the normalised solution is better) you'd need to go with a Global Temp table rather than just a normal one.

The problem with Global Temp Tables is they destroy the ability to run concurrent code.

You don't need a Global Temp Table for this. You can create a Temp Table with just the "backbone" columns and add columns to it on the fly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)