sql - insert into column in target table based on column name in query table

I have a tableB that has 35 columns and many hundreds of thousand rows (each row is also unique).

+------------------------------------------------------------------------------+
|Date | ID | Name | Blah1 | Blah2 | ... | Hour1 | Hour2 | Hour3 | ... | Hour24 |
+------------------------------------------------------------------------------+

Each row in TableB has hourly observations and some other house keeping information.

I want to insert into another table (TableA) based on querying TableB, such that a column in TableA (Hour) gets assigned the value 1 for column Hour1, 2 for column Hour2, 3 for column Hour3 and so on AND the column ObservationValue gets assigned the value in Hour1, Hour2, Hour3... to Hour24. So TableA is in time series representation.

TableA looks like so:

+------------------------------------------------+
|Date| ID| Name | Zone | Hour | ObservationValue |
+------------------------------------------------+

I am not sure at all how I can do this. I know how to insert into a table based on queries but don't know how to perform that operation I want to do.

Appreciate the help.

EDIT

Sample data (I'm not sure if the design of TableB is the best but this was done many years before I inherited the system).

TableB:

8/1/2006 12:00:00 AM 48590 Miami Florida Blah Blah Blah Blah 69.89 63.55 50.73 45.05 44.17 50.39 63.93 84.73 87.61 102.43 123.39 154.33 181.1 232.71 262.11 290.64 299.44   262.67 214.22 191.15 172.49 132.62 105.83 98.25

The above is an example of one record. In this case the ID=48590, Name=Miami, Zone=Florida. There is an entry for each day of the year for 10 years. There are roughly 10,000 ID's in this table.

TableA:

8/1/2006 12:00:00 AM 48590 Miami Florida 1 69.89
8/1/2006 12:00:00 AM 48590 Miami Florida 2 63.55 
8/1/2006 12:00:00 AM 48590 Miami Florida 3 50.73
8/1/2006 12:00:00 AM 48590 Miami Florida 4 45.05
8/1/2006 12:00:00 AM 48590 Miami Florida 5 44.17 
8/1/2006 12:00:00 AM 48590 Miami Florida 6 50.39 
8/1/2006 12:00:00 AM 48590 Miami Florida 7 63.93 
8/1/2006 12:00:00 AM 48590 Miami Florida 8 84.73 
8/1/2006 12:00:00 AM 48590 Miami Florida 9 87.61 
8/1/2006 12:00:00 AM 48590 Miami Florida 10 102.43 
8/1/2006 12:00:00 AM 48590 Miami Florida 11 123.39 
8/1/2006 12:00:00 AM 48590 Miami Florida 12 154.33 
8/1/2006 12:00:00 AM 48590 Miami Florida 13 181.1 
8/1/2006 12:00:00 AM 48590 Miami Florida 14 232.71 
8/1/2006 12:00:00 AM 48590 Miami Florida 15 262.11 
8/1/2006 12:00:00 AM 48590 Miami Florida 16 290.64 
8/1/2006 12:00:00 AM 48590 Miami Florida 17 299.44  
8/1/2006 12:00:00 AM 48590 Miami Florida 18 262.67 
8/1/2006 12:00:00 AM 48590 Miami Florida 19 214.22 
8/1/2006 12:00:00 AM 48590 Miami Florida 20 191.15 
8/1/2006 12:00:00 AM 48590 Miami Florida 21 172.49 
8/1/2006 12:00:00 AM 48590 Miami Florida 22 132.62 
8/1/2006 12:00:00 AM 48590 Miami Florida 23 105.83 
8/1/2006 12:00:00 AM 48590 Miami Florida 24 98.25

The other question I have is, what structure is better if I intend to do arithmetic operations such as subtracting the ObservationValue for a given ID from a ObservationValue for the same ID but in another table? Is TableA optimal for that type of operaiton or is it better to have the TableB structure?

Answers


It sounds like you want to UNPIVOT the data in TableB to insert it into TableA. This will take the column values in tableB and convert it into rows:

-- insert into tableA (Date, Id, Name, Zone, hour, observationvalue)
select Date,
  Id,
  Name,
  Zone,
  replace(hour, 'hour', '') hour,
  observationvalue
from tableB
unpivot
(
  observationvalue
  for hour in (Hour1, Hour2, Hour3, Hour4, Hour5...)
) unpiv;

See SQL Fiddle with Demo


Need Your Help

How to reload a div from a form inside itself?

php ajax forms html reload

I am working on a webpage where I have a main landing-page that has a div that loads different pages; I did this so the entire page doesn't reload but ajax just sends out a call to load a different...

Warning: implode() : Invalid arguments passed in Form Check box

php html sql

Been struggling with this some time now and is probably something simple...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.