table creation - 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

General SQL Server Forums

New to SQL Server Programming

table creation

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:08:19

Hey..
Can i create table dynamically...
ie., When a user register his name aa table must create for him ..
How to do this ...
Help me..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-16 : 11:13:58

Why each user should have its own table?
I think that is a bad design.

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

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-16 : 11:14:12

Yes you can use the dynamic query to do that:

eg-:

DECLARE @user_regitered VARCHAR(100)
DECLARE @sql VARCHAR(1000)

SET @user_regitered = 'aa'

SET @sql = 'CREATE TABLE '+ @user_regitered +' ( column_1 DataType,column_2 DataType..)'
EXEC @sql

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-16 : 11:15:57

quote:
Originally posted by webfred

Why each user should have its own table?
I think that is a bad design.

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

I agree that is a very bad design and will bloat the size of database in future if the number of user increases to a optimum value.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:21:40

quote:
Originally posted by rohitvishwakarma

Yes you can use the dynamic query to do that:

eg-:

DECLARE @user_regitered VARCHAR(100)
DECLARE @sql VARCHAR(1000)

SET @user_regitered = 'aa'

SET @sql = 'CREATE TABLE '+ @user_regitered +' ( column_1 DataType,column_2 DataType..)'
EXEC @sql

Nice idea...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:22:27

I agree with you it is a bad design ..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:24:48

Every user will have their own table ...And it will be truncated before daily use ..Only at present data will be there ... So I agreed for this design...
Tax for the response guys..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:27:06

One doubt i need to create table like this format (tbl_login name_client)
login name will be supplied rest must automatically inserted

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:28:41

as like the same format truncate must be done .. How to do this ?..Help me..

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-16 : 11:29:05

quote:
Originally posted by jafrywilson

One doubt i need to create table like this format (tbl_login name_client)
login name will be supplied rest must automatically inserted

DECLARE @user_regitered VARCHAR(100)
DECLARE @sql VARCHAR(1000)

SET @user_regitered = 'aa' -- login name supplied

SET @sql = 'CREATE TABLE tbl_'+ @user_regitered +'_client ( column_1 DataType,column_2 DataType..)'
EXEC @sql

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-16 : 11:44:18

can i use the same format for truncate table

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-16 : 11:58:22

quote:
Originally posted by jafrywilson

can i use the same format for truncate table

Loop through the login names and store it in some variable & truncate the tables:
eg:-

DECLARE @count INT
DECLARE @max INT
CREATE TABLE #temp
(
id INT IDENTITY(1,1),
login_name VARCHAR(100)
)

INSERT INTO #temp
SELECT login_names FROM your_user_table

SET @count = 1
SET @max = (SELECT COUNT(*) FROM #temp)

WHILE(@count <= @max )
BEGIN
DECLARE @user_name VARCHAR(100)
DECLARE @sql VARCHAR(1000)

SELECT @user_name =login_name FROM #temp WHERE id = @count

SET @sql = TRUNCATE TABLE tbl_'+ @user_name +'_client'
EXEC @sql
SET @count = @count + 1

END

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 00:39:00

Tnx for the help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources