SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Suggestions on creating new table


Need Suggestions on creating new table

Author
Message
yuvipoy
yuvipoy
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 1387
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!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26696 Visits: 17557
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.

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)
yuvipoy
yuvipoy
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 1387
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26696 Visits: 17557
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.

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)
yuvipoy
yuvipoy
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 1387
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!
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5438 Visits: 4076
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;-)
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5438 Visits: 4076
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;-)
yuvipoy
yuvipoy
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 1387
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search