• 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/