Split large table into smaller tables of equal size.

  • 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/

  • 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.

  • 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/

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply