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

Split large table into smaller tables of equal size. Expand / Collapse
Author
Message
Posted Friday, November 4, 2011 2:29 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 3, 2013 8:44 AM
Points: 646, Visits: 187
I am looking for a script I can run that will take a large output table and split it into several smaller tables of approximately equal size. The large table is an order detail table used to generate an email notice to customers. The customers can not span multiple tables as I only want to send one email per customer. I attach the tables to a 3rd party app that generates the email message.

I have no idea where to start on this and need some direction.

Any ideas and code examples are appreciated.





For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1200897
Posted Friday, November 4, 2011 2:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 10,206, Visits: 13,152
What is the column and value that you would be splitting on? Are you working within a single database or will the new tables be in a separate database?

Depending on how many tables you are looking out you could just do multiple

Select 
column_list
INTO
new_table_name
from
order_details
where
partition_column = [value]

To start and then convert them into

INSERT INTO new_table_name
Select
column_list
From
order_details
Where
partition_column = [value]

'

for the ongoing process.

There are other options and you could automate it a bit.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1200911
Posted Friday, November 4, 2011 3:05 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 3, 2013 8:44 AM
Points: 646, Visits: 187
I don't know why I didn't think about just making multiple tables and inserting the data I want into each. Thanks Jack!




For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1200916
Posted Friday, November 4, 2011 4:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
Look into partitioned views. That may get you where you need to be, and will ease insert/deletion/lookups as well.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1200928
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse