Dynamic Table Name - 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

SQL Server Development (2000)

Dynamic Table Name

Author

Topic

joework
Starting Member

1 Post

Posted - 2002-10-14 : 14:15:18

I'm stuck. I'm wanting to insert into a table. The table name is based on who is logged in and will be different for each user. I'm trying to pass in the table name as a parameter but can't seem to get the syntax right. Am I anywhere close? Thanks

Create Procedure dynamicTableName

@tableName varchar(50),
@firstName varchar(50)
as

insert into @tableName (firstName) values (@firstName)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-14 : 16:15:59

I believe you'll need to use Dynamic SQL to do this. Check these articles:

[url]http://www.sqlteam.com/item.asp?ItemID=4619[/url]
[url]http://www.sqlteam.com/item.asp?ItemID=4599[/url]

But I honestly have to question the wisdom of having personal tables for each user... Seems that you could store any personal info a single table for everyone with a UserID field to distinguish.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-10-15 : 13:23:56

I'm with AjarnMark on using a single table with a userID column. Better all the way around.

However, this has piqued my interest. If you know the table name based on the user, you can certainly look up its object ID in sysobjects. Can anyone think of a way to do an insert on an object ID?

-b

sherrer

64 Posts

Posted - 2002-10-17 : 11:05:41

I am totally with the last two replies. I can't see the design advatages. I have tried (in some context I don't remember now) to use the ID in sysobjects. It seems that it is possible, but I never found a way that worked.

Since you are looking for an answer though...

The only way I can think to do it, is to build the SQL into a string variable and use sp_executesql to create your table.

declare @SQL nvarchar(500)

set @SQL = ' select ''' + @firstName + ''' as firstName ' + 'Into ' + @tableName

execute sp_executesql @SQL

Hope this helps... I would love an explination of what you are trying to gain by coding this way.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources