Breaking Tables into numerous smaller tables

Question

Breaking Tables into numerous smaller tables

Ghanta

Hall of Fame

Points: 3999
More actions
December 22, 2007 at 5:40 pm

#62723

Hey Guys,
I have 10 tables of Individuals info (id, street address, city, state, ...) broken down by starting digit of Zipcode... 0..9. I need to break these 10 tables into 50 tables 1 for each state ... Each state will have its own table... What is the best way to do this?
Thanks a lot for your time and assistance.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

That's an ugly design. Just curious, why would you want that anyway? One table with a suitable index would (in just about all cases) be better, and certainly easier to use (depending on your client tools I suppose).

Anyway, you'll need lots of ugly dynamic SQL. If you just need to do this once, or to generate the code once, then you could do something like

create view vMergedPostcodeTables --this is for convenience

as

select * from tbl_postcode_1

UNION ALL

select * from tbl_postcode_2

UNION ALL

select * from tbl_postcode_3

UNION ALL

etc, etc

Then do

select 'select * into [tbl_state_' + state + '] from vMergedPostcodeTables where state = ' + quoteName(state, '''')

from (select distinct state from vMergedPostcodeTables)

This will generate all of your select * into [state_table] from Postcodes for each state. Copy the output and paste into a new query window.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

I need to break these 10 tables into 50 tables 1 for each state ... Each state will have its own table... What is the best way to do this?

Please explain why you need to do this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

Basically I have 10 tables of Individuals Info broken down by First Digit of Zip... like all the zip starting from 1 has table called tble1 and so on... I need to use those 10 tables and break them into 50 tables by state... all the tables have state column too... Total number of records from all tables is around 40 million. Thanks for the help... Do you think I am better off creating a huge table with all 40 million records and then breaking them by using where clause?

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

Yes, and place an index on the State column. There's jus no logical reason that i can think of to seperate the data when it's all related. I can see your successive queries being rather difficult to manage. I mean, you'd have to determine which table to query based on which state and this just doesn't make sense. at least to me.. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ghanta Hall of Fame Points: 3999 More actions · Answer 5

Thanks for the help... actually those 50 tables have to be further matched against a huge table to append other info based on addresses.... I did not want to run that update on this huge table.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

Well if you restrict your updates by state and have the state column indexed, i believe the updates will take just as long as if you had them in separate tables.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ghanta Hall of Fame Points: 3999 More actions · Answer 7

Ghanta

Hall of Fame

Points: 3999

December 22, 2007 at 8:10 pm

#762428

Thanks guys

Ghanta Hall of Fame Points: 3999 More actions · Answer 8

Jeff Moden (12/22/2007)
I need to break these 10 tables into 50 tables 1 for each state ... Each state will have its own table... What is the best way to do this?
Please explain why you need to do this...

I have one table now with index on State column; however, when I run the above script I get syntax error near ')'... Any help . thanks!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Which "above script"... don't waste time, be specific, please.

You also said you wanted to update the table with address info... heh... what if a person has 2 addresses?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve block SSCertifiable Points: 7698 More actions · Answer 10

Could you post the file layout of the table in question? You may need to normalize it instead of breaking it into separate tables based upon artificial criteria.

Steve

Ghanta Hall of Fame Points: 3999 More actions · Answer 11

Thanks a lot guys ... with all yours suggestion, I was able to complete this. Happy new year.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Please share what you did...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)