I need to create multiple tables from a single table based on unique column values

Posted on 2013-10-01
Medium Priority
Last Modified: 2013-10-30
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.
Question by:CalBob
  • 2
  • 2
  • 2
  • +3
LVL 18

Expert Comment

ID: 39537220
I'm curious to why you want to create separate tables? That is contrary to typical database development.
How long do the tables exist?
LVL 18

Accepted Solution

x-men earned 2000 total points
ID: 39537267
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...
LVL 18

Expert Comment

ID: 39537272
or better yet...create a VIEW for each salesman.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 13

Expert Comment

ID: 39537582

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.

LVL 27

Expert Comment

by:Chris Luttrell
ID: 39537659
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.
LVL 49

Expert Comment

ID: 39542401
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

Author Comment

ID: 39543655
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.
LVL 27

Expert Comment

by:Chris Luttrell
ID: 39544164
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.

Author Comment

ID: 39544189
I am familiar with Reporting Services.  Thanks for the suggestion.  I will look at that and see if that will work for this project.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question