Dynamic Naming Temp Table - SQL Server Forums

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)

Dynamic Naming Temp Table

Author

Topic

rSyn
Starting Member

5 Posts

Posted - 2005-02-02 : 03:51:18

Hi,

is it possible to naming temp table dynamically ?? ie : #t_20050131 it represent the date (20050131) and in the next day it will automically create temp table named #t_20050201 ???

Any suggestion will appreciate

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 04:03:20

Why?
A temp table only lasts for the life of the connection and is dropped when the connection is.
What are you trying to do?

Andy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-02 : 04:27:25

Anyway you can try this

declare @date varchar(10)
declare @sql varchar(100)
select @date=convert(decimal ,d )from (
Select (ltrim(str(year(getdate()))) + case when len(ltrim(str(month(getdate()))))=1 then
'0'+ltrim(str(month(getdate()))) else ltrim(str(month(getdate()))) end + ltrim(str(day(getdate()-1)))) as d) as t
set @date= 't_'+@date
set @sql='Create table '+@date+' (n numeric)'
print @sql
exec (@sql)

Madhivanan

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 04:35:37

Madhivanan
Instead of

quote:

select @date=convert(decimal ,d )from (
Select (ltrim(str(year(getdate()))) + case when len(ltrim(str(month(getdate()))))=1 then
'0'+ltrim(str(month(getdate()))) else ltrim(str(month(getdate()))) end + ltrim(str(day(getdate()-1)))) as d) as t

Why not just do this

SET @date = CONVERT(varchar(10),DATEADD(dd,-1,GETDATE()),112)
PRINT @date
20050201

Andy

Edit: Added quote

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-02 : 05:10:48

Thanks Andy. Yours is so easy

Madhivanan

rSyn
Starting Member

5 Posts

Posted - 2005-02-02 : 23:52:57

Thank You.....it's Work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources