Need Suggestions on creating new table

  • Hi,

    I am having a condition that i need to create tables on run time that is

    Say for customer 1, if logins he will be giving some data which is not predefined, that while after logging in he will be saying that these is his data so based on that there will be interface between customer and DB which will give me the structure saying that these are the datatypes of each columns say if it is having 100 columns with data the interface will send to DB saying that these are the columns with their datatypes so on receiving then i need to create a table dynamically with the name given by customer(which will be from interface),after that i need to insert the data accordingly.After that customer will ask for some information so i need to have a select statement dynamically and same for update also.

    for customer 2 there may be 120 columns(need to create table with 120 columns dynamically)

    for customer 3 there may be 80 columns(need to create table with 80 columns dynamically)

    for customer 4 there may be 70 columns (need to create table with 70 columns dynamically)

    and so on.....

    there are N number of customers are there.

    Per day there may be some 1000 customers(max)

    What is the better way for this one?

    Thanks!

  • yuvipoy (12/26/2012)


    Hi,

    I am having a condition that i need to create tables on run time that is

    Say for customer 1, if logins he will be giving some data which is not predefined, that while after logging in he will be saying that these is his data so based on that there will be interface between customer and DB which will give me the structure saying that these are the datatypes of each columns say if it is having 100 columns with data the interface will send to DB saying that these are the columns with their datatypes so on receiving then i need to create a table dynamically with the name given by customer(which will be from interface),after that i need to insert the data accordingly.After that customer will ask for some information so i need to have a select statement dynamically and same for update also.

    for customer 2 there may be 120 columns(need to create table with 120 columns dynamically)

    for customer 3 there may be 80 columns(need to create table with 80 columns dynamically)

    for customer 4 there may be 70 columns (need to create table with 70 columns dynamically)

    and so on.....

    there are N number of customers are there.

    Per day there may be some 1000 customers(max)

    What is the better way for this one?

    Thanks!

    This sounds like an absolute nightmare. Is there any consistency about what data is being stored or is the number of columns and datatypes completely customizable? What need to happen with this data? If it is just a repository that is one thing but if you need to do anything more than basic insert/update/delete this is going to become unbelievably complicated.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • These data hold the data of that customer related details which will be joined with some basic tables.There are some master tables are there and need to join with upcoming new table and give the result. For master table before inserting into this table it will have the record.

    I knew that this is more complex but my requirement was like that :crazy: :angry:

  • Well unless you can share a LOT more details there is not much I can offer. This sounds like a challenging situation.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is the situation

    I need to create tables dynamically on run time the schema of the table will be given by customer and same time he will give the data to insert.

    Needs to fetch data from newly created table with some master table on run time, need a join query on run time and fetch the data ,which will be used for update as well as select/delete.

    master_table1:20 columns

    master_table2:10 columns

    cust1:100 columns

    cust2:120 columns

    .

    .

    .

    Need to generate dynamic query on run time say for cust1 with master_table1 and master_table2 which has common id's in all the three tables, all this will be happen at run time.

    Dynamic insert also needs to takes place after table is been created i.e after the new table has been created the data for that table will be populated accordingly.

    Thanks!

  • you need to create an metadata table (say... customer_id, table name , column_name )which wil contain column list per customer level (assuming every customer will have definite columns list )

    then once any customer calls ... the related columns will get picked up and used.

    this is small output/suggestion based on your small input/details:-D

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • one for thing , i cant tell how it will behave from performance perspective.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Your are exactly right it is a meta data kind f thing only from meta data i will be getting this information,but there is no hard-cord rule like thing there is no default table like customer table to pick and use those attributes.On the meta data level i will be getting the column name with data-types,on getting those i need to create tables and insert data.

    Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply