Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    13

    Unanswered: creating /accessing tables with dynamic names

    Hi
    I have developed an application in ASP/SQL server 7.
    Ths system is single user. One of the tables is updated by usr actions( say Table A).
    To make it multi user. I want to create table such as A_Username.
    How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
    Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
    pls suggest

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest one table for everyone to share, with the username as a column.

    -PatP

  3. #3
    Join Date
    Nov 2003
    Posts
    13
    All rows in the table will be accessible to every users..The problem is that Updation made by one usr must not reflect for other user.. He must be able to view it independently of any other users.. hence i wanted to make seperate tables of same definition.. thn when user logs out the table wud be deleted

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Then do what Pat suggested and implement a view or stored procedure appropriately.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Nov 2003
    Posts
    13
    Hi Pat
    I m unable to understand the solution suggested by u?? i hve one table to be accessed by different users on diff machines at the same time,.. ZThey wil call stored procedures to access the table and update it.. Updation of User 1 must not be reflected when user to is updating it or viewing it.. It is like i want multiple copies and updations/access etc on these copies..These copies wud get created when user logs in and get deleted when user logs out.. How wud the stored procedure written refer to copies(currently it refers to the table name..for eg.. select * from table A) .. I didnt understand ur suggestion abt using username as a column.. If hve 100000 rows in the table and 5 users.. how wud it be put in the table and used? pls elaborate as i m quite new to databases

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What you are thinking of is this :
    Code:
    use pubs
    go
    Create table A_TableName ( col1 int,col2 int)
    Create table B_TableName ( col1 int,col2 int)
    Create table C_TableName ( col1 int,col2 int)
    go
    insert into A_TableName 
    select 1,1
    union
    select 2,1
    union
    select 3,1
    union
    select 4,1
    go 
    insert into B_TableName 
    select 1,1
    union
    select 2,1
    union
    select 3,1
    union
    select 4,1
    go
    insert into C_TableName 
    select 1,1
    union
    select 2,1
    union
    select 3,1
    union
    select 4,1
    go
    select col1,col2 from A_TableName
    select col1,col2 from B_TableName
    select col1,col2 from C_TableName
    go
    drop table A_TableName
    drop table B_TableName
    drop table C_TableName
    go
    What PAT recommends is this
    Code:
    Use pubs
    go
    Create table TableName (UserName varchar(10), col1 int,col2 int)
    go
    insert into TableName 
    select 'A',1,1
    union
    select 'A',2,1
    union
    select 'A',3,1
    union
    select 'A',4,1
    union
    select 'B',1,1
    union
    select 'B',2,1
    union
    select 'B',3,1
    union
    select 'B',4,1
    union
    select 'C',1,1
    union
    select 'C',2,1
    union
    select 'C',3,1
    union
    select 'C',4,1
    go
    select col1,col2 from Tablename where username = 'A'
    select col1,col2 from Tablename where username = 'B'
    select col1,col2 from Tablename where username = 'C'
    go
    drop table TableName
    go
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Nov 2003
    Posts
    13
    Hi
    In my case.. The table will hve around 100000-200000 rows.. User will click a button in front end.. then rows from a master table will be inserted into this table..On each click one record goes from master table to the updatable table.. Stores procs are called to process records in the 2nd table.. and then results are displayed.
    each user will be parallely accessing the master table and wud be doing the insertion and updation... Result displayed wud hve to be as per his click position.. irrespective of the other users clicks ..
    I need to hve a seperate work area for each user and results displayed as per the work stage he/she has reached.
    Is there some way to create multiple views of a table in which rows are added and updated and then displayed..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •