Need help reorganizing db with 3000 tables!

I am somewhat of a noob and perhaps someone out there may have an idea how to help me.

I have a single database with over 3000 tables. Each table is simply a variable read from a production machine (complex, to say the least). Each table has only 2 fields - Read_time and Value. Some tables have only a few records, while others have hundreds (1 record every 10 seconds).

What I am trying to do is to reorg the 3000 tables into 1 with Read_time being a key field. Something like: Read_time | Varname1 |Varname2|Varname3|... timestamp1| Value |Value |Value |... timestamp2| Value .... .... ... timestamp3|....

Any hot tips for me? I have never seen a database organized this way before. Sorry if my similated table doesn't look much like a table. It looked ok when I edited it.

more ▼

asked Jan 28, 2010 at 03:38 PM in Default

avatar image

Leo 2
1 1 1 1

What are your table names like? Random IDs or standardized?

Jan 28, 2010 at 04:17 PM Blackhawk-17

you'd like hot tips on what the new table should look like, or on the implementation in t-sql on consolidating your tables?

Jan 29, 2010 at 03:45 AM Nick Kavadias
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Without a more complete set of requirements, any suggestions made should be taken with a large grain of salt...

From what you've laid out, the basic approach would be to get a table with three fields.

FieldA = The original table name, which I assume reflects a client or app or some other category. I'd make it a field with an fk to another table that actually stores the name FieldB = Read_Time FieldC = Value

I don't see why you'd have multiple Read_Time & Value fields based on what you provided for requirements. So in short two tables, one that is just there as a look for data consistency.

more ▼

answered Jan 28, 2010 at 03:52 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

Grant's suggestion makes perfect sense to collapse the 3000 tables to a single table if what you're looking it are truly 3k tables w/ two columns, Read_Time and Value, which is how you've described the tables.

But, I'm not sure what this was supposed to be:

Read_time | Varname1 |Varname2|Varname3|... timestamp1| Value |Value |Value |... timestamp2| Value .... .... ... timestamp3|

I'm guessing that we need to explore your requirements a bit further. In the current tables, you mention some have a few rows, others hundreds. In each tables, is that value type always the same, or could the values be for different types of things (within a single table)?

more ▼

answered Jan 28, 2010 at 11:55 PM

avatar image

Jay Bonk
1.4k 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 28, 2010 at 03:38 PM

Seen: 584 times

Last Updated: Jan 28, 2010 at 03:38 PM

Copyright 2018 Redgate Software. Privacy Policy