Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Suggestions on creating new table Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 6:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!
Post #1400221
Posted Wednesday, December 26, 2012 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400254
Posted Wednesday, December 26, 2012 10:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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
Post #1400295
Posted Wednesday, December 26, 2012 10:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400301
Posted Wednesday, December 26, 2012 11:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!
Post #1400468
Posted Thursday, December 27, 2012 2:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1400502
Posted Thursday, December 27, 2012 2:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1400503
Posted Thursday, December 27, 2012 5:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!
Post #1400576
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse