How to create a temporary table where the name of the table is the user's
ID.
#@.IDUsuarioYou can't unless you use dynamic sql but then the table only lasts as long
as the dynamic sql scope. If every table will be unique to that user why not
create a real table instead?
Andrew J. Kelly SQL MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:OqFFzMxFGHA.2040@.TK2MSFTNGP14.phx.gbl...
> How to create a temporary table where the name of the table is the user's
> ID.
> #@.IDUsuario
>|||Why would you want to do that? Temp tables internal names are unique, so you
don't have to worry
about duplicates, and one session cannot see another sessions temptable anyh
ow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Dulk" <fdulk@.bol.com.br> wrote in message news:OqFFzMxFGHA.2040@.TK2MSFTNGP14.phx.gbl
..
> How to create a temporary table where the name of the table is the user's
> ID.
> #@.IDUsuario
>
Showing posts with label temporary. Show all posts
Showing posts with label temporary. Show all posts
Tuesday, March 27, 2012
CREATE a Temp Table via "EXEC (@SQL)"
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:
--------------------------
-- Get all SubquestionIDs for this concept
--------------------------
DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR
SELECT QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
FROM RotationMaster AS RM
INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation
INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber
INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumber
WHERE RM.Study = @.Study
GROUP BY QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
HAVING QGDM.SubquestionID <> 0
--------------------------
-- Dynamically create a Temp Table to store the data, simulating a pivot table
--------------------------
SET @.Count = 2
SET @.SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'
OPEN curStudySubquestions
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = @.SQL + ', Col' + CAST(@.Count AS VARCHAR(5)) + ' VARCHAR(10)'
SET @.Count = @.Count + 1
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
END
SET @.SQL = @.SQL + ', ShowOrder SMALLINT)'
CLOSE curStudySubquestions
PRINT 'Create Table SQL:'
PRINT @.SQL
EXEC (@.SQL)
SET @.ErrNum = @.@.ERROR
IF (@.ErrNum <> 0)
BEGIN
PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!'
RETURN
END
PRINT '*** Table Created ***'
-- Test that the table was created
SELECT *, 'TEST' AS AnyField FROM #AllSubquestions
The line PRINT @.SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):
CREATE TABLE #AllSubquestions
(Col1 VARCHAR(100),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(10),
Col7 VARCHAR(10),
ShowOrder SMALLINT)
However, the SELECT statement to test the creation of the table produces this error:
*** Table Created ***
Server: Msg 208, Level 16, State 1, Procedure
sp_SLIDE_CONCEPT_AllSubquestions, Line 73
Invalid object name '#AllSubquestions'.
It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?The "select *" is outside of the transaction that created the temp table. The way you have written the proc, the temp table disappears at the conclusion of the exec(@.sql).
I would suggest you create a unique static table name (not a temp table) that you feed to the exec sql, then reference the table later as needed. The do the right thing and clean-up after yourself before you exit the sproc.|||The problem is that this temp table will have a variable number of fields when it is run, so a static table won't work. It would be messy to figure out which fields contain data. Oh, and that is only a part of the SP, I clean up properly :)
What I tried was creating "##Allsubquestions", and the table remains in scope for the rest of the SP.
Maybe, though, there is a better way of doing this? Suppose this study has 4 questions, repeating once per Concept. I need the data in this temp table like this:
Col1 Col2 (Question1) Col3 (Question2) Col4 (Question3) Col5 (Question4) ShowOrder
-------------------------------------
Concept 1 25 86 15 47 1
Concept 2 35 89 54 72 2
Concept 3 69 17 48 36 3
Norm A 50 40 40 30 100
Norm B 54 38 42 36 101
This SP will return a recordset to a generic routine that will populate a MS Graph in a PowerPoint presentation, so I need return a dataset that is formatted where I can just load the Graph's Datasheet object from the SP result set. What will change from study to study is the number of questions. This example has four. There may be 8, 10, or any other number.
--------------------------
-- Get all SubquestionIDs for this concept
--------------------------
DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR
SELECT QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
FROM RotationMaster AS RM
INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation
INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber
INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumber
WHERE RM.Study = @.Study
GROUP BY QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
HAVING QGDM.SubquestionID <> 0
--------------------------
-- Dynamically create a Temp Table to store the data, simulating a pivot table
--------------------------
SET @.Count = 2
SET @.SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'
OPEN curStudySubquestions
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = @.SQL + ', Col' + CAST(@.Count AS VARCHAR(5)) + ' VARCHAR(10)'
SET @.Count = @.Count + 1
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
END
SET @.SQL = @.SQL + ', ShowOrder SMALLINT)'
CLOSE curStudySubquestions
PRINT 'Create Table SQL:'
PRINT @.SQL
EXEC (@.SQL)
SET @.ErrNum = @.@.ERROR
IF (@.ErrNum <> 0)
BEGIN
PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!'
RETURN
END
PRINT '*** Table Created ***'
-- Test that the table was created
SELECT *, 'TEST' AS AnyField FROM #AllSubquestions
The line PRINT @.SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):
CREATE TABLE #AllSubquestions
(Col1 VARCHAR(100),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(10),
Col7 VARCHAR(10),
ShowOrder SMALLINT)
However, the SELECT statement to test the creation of the table produces this error:
*** Table Created ***
Server: Msg 208, Level 16, State 1, Procedure
sp_SLIDE_CONCEPT_AllSubquestions, Line 73
Invalid object name '#AllSubquestions'.
It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?The "select *" is outside of the transaction that created the temp table. The way you have written the proc, the temp table disappears at the conclusion of the exec(@.sql).
I would suggest you create a unique static table name (not a temp table) that you feed to the exec sql, then reference the table later as needed. The do the right thing and clean-up after yourself before you exit the sproc.|||The problem is that this temp table will have a variable number of fields when it is run, so a static table won't work. It would be messy to figure out which fields contain data. Oh, and that is only a part of the SP, I clean up properly :)
What I tried was creating "##Allsubquestions", and the table remains in scope for the rest of the SP.
Maybe, though, there is a better way of doing this? Suppose this study has 4 questions, repeating once per Concept. I need the data in this temp table like this:
Col1 Col2 (Question1) Col3 (Question2) Col4 (Question3) Col5 (Question4) ShowOrder
-------------------------------------
Concept 1 25 86 15 47 1
Concept 2 35 89 54 72 2
Concept 3 69 17 48 36 3
Norm A 50 40 40 30 100
Norm B 54 38 42 36 101
This SP will return a recordset to a generic routine that will populate a MS Graph in a PowerPoint presentation, so I need return a dataset that is formatted where I can just load the Graph's Datasheet object from the SP result set. What will change from study to study is the number of questions. This example has four. There may be 8, 10, or any other number.
Create a Table using another table!
Hi,
Sorry to trouble you all, but I am not getting anywhere!
I need to copy the contents of 1 table into a new temporary table. What I am
doing is :
SELECT *
FROM U_regulargiver
WHERE Status ='I'
OR Status ='N'
INTO TABLE #U_T_Committed
Obviously I'm doing something wrong, but I can't seem to figure out exactly
what. Can anyone please help?
Thanks in advance
RobYou screwed up with the syntax:
> SELECT *
> INTO TABLE #U_T_Committed
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7BE6CA67-EF93-4A97-8BA7-22220C06E0FA@.microsoft.com...
> Hi,
> Sorry to trouble you all, but I am not getting anywhere!
> I need to copy the contents of 1 table into a new temporary table. What I
> am
> doing is :
> SELECT *
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
> INTO TABLE #U_T_Committed
> Obviously I'm doing something wrong, but I can't seem to figure out
> exactly
> what. Can anyone please help?
> Thanks in advance
> Rob
>|||You can get the results you want by writing the statement this way.
Create Table #temp
(
Test nvarchar (10),
Description nvarchar (255)
)
select * from testuser.dbo.MyTable
Insert into #temp
Select *
from testuser.dbo.MyTable
Select * from #temp
Drop table #temp
refer to Books Online for more information if you must use the INTO clause.
"Robert" wrote:
> Hi,
> Sorry to trouble you all, but I am not getting anywhere!
> I need to copy the contents of 1 table into a new temporary table. What I
am
> doing is :
> SELECT *
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
> INTO TABLE #U_T_Committed
> Obviously I'm doing something wrong, but I can't seem to figure out exactl
y
> what. Can anyone please help?
> Thanks in advance
> Rob
>
Sorry to trouble you all, but I am not getting anywhere!
I need to copy the contents of 1 table into a new temporary table. What I am
doing is :
SELECT *
FROM U_regulargiver
WHERE Status ='I'
OR Status ='N'
INTO TABLE #U_T_Committed
Obviously I'm doing something wrong, but I can't seem to figure out exactly
what. Can anyone please help?
Thanks in advance
RobYou screwed up with the syntax:
> SELECT *
> INTO TABLE #U_T_Committed
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7BE6CA67-EF93-4A97-8BA7-22220C06E0FA@.microsoft.com...
> Hi,
> Sorry to trouble you all, but I am not getting anywhere!
> I need to copy the contents of 1 table into a new temporary table. What I
> am
> doing is :
> SELECT *
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
> INTO TABLE #U_T_Committed
> Obviously I'm doing something wrong, but I can't seem to figure out
> exactly
> what. Can anyone please help?
> Thanks in advance
> Rob
>|||You can get the results you want by writing the statement this way.
Create Table #temp
(
Test nvarchar (10),
Description nvarchar (255)
)
select * from testuser.dbo.MyTable
Insert into #temp
Select *
from testuser.dbo.MyTable
Select * from #temp
Drop table #temp
refer to Books Online for more information if you must use the INTO clause.
"Robert" wrote:
> Hi,
> Sorry to trouble you all, but I am not getting anywhere!
> I need to copy the contents of 1 table into a new temporary table. What I
am
> doing is :
> SELECT *
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
> INTO TABLE #U_T_Committed
> Obviously I'm doing something wrong, but I can't seem to figure out exactl
y
> what. Can anyone please help?
> Thanks in advance
> Rob
>
Subscribe to:
Posts (Atom)