CalBob
asked on
I need to create multiple tables from a single table based on unique column values
I have a large table of say, 1,000,000 records. One of the columns in the table identifies a salesman's name. Let's say there are 50 unique salesmen. What I need to do is make a separate table for each salesman from the large table. Each salesman's table would include rows from the large table associated with that sales person. I don't always know the salesmen that will be in the table or how many. In other words, one time there may be 50 salesmen in the table, so I need to create 50 tables, other times there may be 45 or 55.
Another way of saying it is, I want to divide the large table into separate tables by sales person. (And each table only contains that sales persons rows/data.) Ideally the tables would be named (or include) the salesman's name in the column.
I hope that is clear. The question seems easier than the solution.
Thanks for any help.
Another way of saying it is, I want to divide the large table into separate tables by sales person. (And each table only contains that sales persons rows/data.) Ideally the tables would be named (or include) the salesman's name in the column.
I hope that is clear. The question seems easier than the solution.
Thanks for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or better yet...create a VIEW for each salesman.
Hello,
Creating multiple tables and/or views is not the good idea. It would increase your maintenance and supportability cost and will make development much more complicated. Even though you can use table partitioning or partitioned views to separate salesman' data into different filegroups/disk array, it rarely makes sense.
If all of your queries are using salesman as the filter, you can add it as the first (leftmost) column to the clustered and nonclustered indexes.
Thanks,
Dmitri
Creating multiple tables and/or views is not the good idea. It would increase your maintenance and supportability cost and will make development much more complicated. Even though you can use table partitioning or partitioned views to separate salesman' data into different filegroups/disk array, it rarely makes sense.
If all of your queries are using salesman as the filter, you can add it as the first (leftmost) column to the clustered and nonclustered indexes.
Thanks,
Dmitri
As everyone else is asking, why are you doing this, what problem are you trying to solve? As Dmitri says above, an proper index strategy is probably what you really need.
Another vote for NOT creating multiple tables
Do please tell us why think making multiple tables is necessary.
By the way, if you needed to produce a report for a sales manager, then you would probably end-up doing something like this:
select salesperson, sum(sales_value), sum(commission) from (
select salesperson, sales_value, commission from sales_person_aa union all
select salesperson, sales_value, commission from sales_person_ab union all
select salesperson, sales_value, commission from sales_person_ac union all
select salesperson, sales_value, commission from sales_person_ad union all
...
select salesperson, sales_value, commission from sales_person_zz
) as massive_union
group by salesperson
Kapow! you just blew away any performance gain from using small tables
Do please tell us why think making multiple tables is necessary.
By the way, if you needed to produce a report for a sales manager, then you would probably end-up doing something like this:
select salesperson, sum(sales_value), sum(commission) from (
select salesperson, sales_value, commission from sales_person_aa union all
select salesperson, sales_value, commission from sales_person_ab union all
select salesperson, sales_value, commission from sales_person_ac union all
select salesperson, sales_value, commission from sales_person_ad union all
...
select salesperson, sales_value, commission from sales_person_zz
) as massive_union
group by salesperson
Kapow! you just blew away any performance gain from using small tables
ASKER
I hear you loud and clear - I'm rethinking multiple tables. In the end, I have to create a separate Excel spreadsheet for each sales person based on their data. That is why I was initially going to create individual tables. Then I would create a spreadsheet from each table. In other words, one Excel file per table. How do I create 50 Excel files from one table? My intent was to export each of the smaller tables to their own Excel file, if that makes sense. Thanks.
Are you familiar with Reporting Services? It has Excel as an export format option and you could look into setting up a data driven subscription to generate the Excel Report for each sales person filtered to their data and email it out on a scheduled basis. We do that with project managers for many reports.
ASKER
I am familiar with Reporting Services. Thanks for the suggestion. I will look at that and see if that will work for this project.
How long do the tables exist?