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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm curious to why you want to create separate tables? That is contrary to typical database development.
How long do the tables exist?
x-menIT super heroCommented:
declare salesmanCursor cursor for select distinct salesman from [big table]
declare @salesman nvarchar(max)
open salesmanCursor
FETCH NEXT FROM salesmanCursor into @salesman
   IF NOT EXISTS (select * from sys.objects where object_id = OBJECT_ID(quotename(@salesman) and type in ('U'))
   CREATE TABLE QUOTENAME(@salesman) ( define all columns here)
select into @salesman from big table where salesman = @salesman

   FETCH NEXT FROM salesmanCursor into @salesman
close salesmanCursor
deallocate salesmanCursor

Probably you'have to convert de table creation into some dynamic SQL, not sure that you can use variables on that...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
x-menIT super heroCommented:
or better yet...create a VIEW for each salesman.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!


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.

Chris LuttrellSenior Database ArchitectCommented:
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
CalBobAuthor Commented:
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.
Chris LuttrellSenior Database ArchitectCommented:
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.
CalBobAuthor Commented:
I am familiar with Reporting Services.  Thanks for the suggestion.  I will look at that and see if that will work for this project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.