Split large table into smaller tables of equal size.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Split large table into smaller tables of...

Split large table into smaller tables of equal size.

Rate Topic

Display Mode

Topic Options

Author

Message

MRitch

Posted Friday, November 04, 2011 2:29 PM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 25, 2012 9:30 AM
Points: 630, Visits: 186

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

Jack Corbett

Posted Friday, November 04, 2011 2:52 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

MRitch

Posted Friday, November 04, 2011 3:05 PM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 25, 2012 9:30 AM
Points: 630, Visits: 186

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

Evil Kraig F

Posted Friday, November 04, 2011 4:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 5,678, Visits: 6,130

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 »

Permissions