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

Question

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?

Please provide sample data for TableB and how it should look in TableA after insert. — Linus Caldwell
– Linus Caldwell, Commented Apr 21, 2013 at 2:26
Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. — Taryn
– Taryn, Commented Apr 21, 2013 at 2:34

Taryn · Accepted Answer · 2013-04-21 02:50:49Z

1

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

answered Apr 21, 2013 at 2:50

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codingknob Over a year ago

Thank you Bluefeet. I will give that a try. I was also wondering, about what structure is better if I intend to do arithmetic operations such as subtracting the ObservationValue for a given ID from the ObservationValue for the same ID but in another table? For example I want to compute the difference between a forecasted value of ObservationValue (occurs the day before and is in another table) from the actual ObservationValue. Is TableA optimal for that type of operation or is it better to have the TableB structure?

Taryn Over a year ago

@algotr8der Your tableB is denormalized and should be altered to use something similar to tableA, then you have the data in a format that can be used for comparison.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related