Creating temp table dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating temp table dynamically

Author

Topic

srinath
Starting Member

16 Posts

Posted - 2006-11-06 : 08:02:19

Hi All,

I am trying to create the temporary table dynamically in the stored procedure as

Exec ('Create table #TEMP(' + @A + ')

I have the values in the variable @A = 'Period1 varchar(25),Period2 varchar(25),Period3 varchar(25),Period4 varchar(25),Period5 varchar(25))

But when I tried to query the table...Its giving an error Invalid object...

I don't want to have the global temporary table and also I don't want to create the table as it may cause us some problems if the same SP is used my more than one person.

Is there any way that we create the temp table in the SP and use that in the same SP.

Thanks in Advance.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 08:08:32

you have to use global temp table. since the scope of the temp table wont allow you to access the table.

read on the book online for the scope of the temp or hash tables, you will get better idea.

what are you trying to do, may be someone over here may give a better option to do it?

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-06 : 08:14:39

Either use global temp tables (##tables) as Chirag said or combine your query command inside the same exec call like this:

exec ('create table #x( a int) insert into #x values(1) select * from #x')

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 08:39:45

to understand temp tables:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-11-06 : 14:45:12

Good article.

The temp table doesnt need to be global to be seen by the procedure in and out of dynamic code, just created before the dynamic code is executed. The table will be accessible in the dynamic code and after it has completed running.. the scope of the temp table will prevent any data problems from having multiple users running the procedure (or even the same user running it multiple times at once). Maybe for the OP's specific needs, just create the temp table with an identity then use dynamic sql to add the columns..


DECLARE @cols varchar(2000) --- this could be an imput parameter
select @cols = 'Period1 varchar(25),Period2 varchar(25),Period3 varchar(25),Period4 varchar(25),Period5 varchar(25)'

CREATE TABLE [#test2]
(	[t2_Key] [int] IDENTITY(1,1) NOT NULL )

declare @sql varchar(2000)
select @sql = 'ALTER TABLE #test2 ADD ' + @cols

exec(@sql) --- this adds the fields to the temp table

The table can now have data added and selected from, by both direct queries and dynamic sql..

srinath
Starting Member

16 Posts

Posted - 2006-11-09 : 03:42:17

As I was busy on some other priority things I didn't get a chance to look at the query, today I did the same way as giveb by samuelclay and it is working ..

Thanks a lot samuelclay & spirit1

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-03-31 : 06:58:48

i have tried samuel way :


set @DynamicSql=' ALTER TABLE #ReportTable ADD date_'+@ReportMonthStart+' bigint'

where i have created at first the table #ReportTable .
i got this error :

quote:
Could not find stored procedure ' ALTER TABLE #ReportTable ADD date_200709 bigint'.

any idea why?
or any other way to do this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-31 : 07:01:10

you'll have to show us more sql than that...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 07:01:36

Missing parenthesis after EXEC, huh?

exec(@sql)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-03-31 : 07:27:37

yes
it was the problem oopps

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

jnich76
Starting Member

1 Post

Posted - 2012-09-28 : 10:33:22

You can use the global temp table to dynamically create the table without declaring it beforehand.

set @sql = 'Select col1, col2 into ##tempF from sometable where 1=1'

exec(@sql)

Select * from ##tempF

WORKS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources