How to create table with unique end on the name.

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 2005 Forums

Transact-SQL (2005)

How to create table with unique end on the name.

Author

Topic

zubamark
Starting Member

23 Posts

Posted - 2009-10-13 : 14:09:09

Hi,

How to add to the end of the table client's id on the fly?

declare @ID varchar(100) set @ID=101

if exists(select * from tempdb..sysobjects where name ='tbl'@ID)
drop table tbl@ID

Create table tbl@ID (ID char(4),CO char(20))
select * from tbl@ID
drop table tbl@ID

It creates tbl@ID, but not tbl101

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 14:22:00

It would work using dynamic sql like this:


declare @ID varchar(100) set @ID=101
exec('if exists(select * from tempdb..sysobjects where name =''tbl' +@ID+ ''') drop table tempdb..tbl'+@ID)
exec('Create table tempdb..tbl' + @ID+ ' (ID char(4),CO char(20))')
exec('select * from tempdb..tbl' +@ID)

But why?

No, you're never too old to Yak'n'Roll if you're too young to die.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2009-10-13 : 14:25:15

I'm not sure why you want to do this as it's not a good design practice... but try dynamic SQL. Here's an example using global temp tables:

declare @ID varchar(100) set @ID=101;
Declare @tablename nvarchar(255); set @tablename = '##tbl' + @ID;

if OBJECT_ID('tempdb.dbo.' + @tablename) IS NOT NULL
BEGIN
EXEC('drop table ' + @tablename);
PRINT('dropped ' + @tablename)
END

EXEC('Create table ' + @tablename + ' (ID char(4),CO char(60))')

EXEC ('INSERT ' + @tablename + ' VALUES(1,''BAD DESIGN ALWAYS HURTS THE ONES YOU LOVE MOST'')');

EXEC ('select * from ' + @tablename);

EXEC('drop table ' + @tablename);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources