I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and pulling the Excel file from another server. I am trying to figure out how I can loop through the columns and pull out the data I need. If I could rearrange the data, I would but am currently stuck with what I have.
In my current table tblHW I have Columns such as PmpCount, , NumberStages, Pmpmodel_pmp1, serialnum_Pmp1, pmpModel_pmp2, Pmpmodel_pmp2, serialnum_pmp2, partnum_motor1, serialnumberMotor1, etc…. I apologize in advance for not being able to post a real table or a picture.
|Name | PmpCount| numstages| pmpmodel_pmp1| stages_pmp1| Sn_pmp1| |AN 91-23G | 4| 500| FX2347| 250| 354197| |BR DN 895R| 5| 521| D2442| 45| 875164| |ALN 1-60J | 5| 521| H21342| 95| 594126|
|pmpmodel_pmp2| stages_pmp2| sn_pmp2| Partnum_mtr1| sn_mtr1| |FX2347 | 250| 354198| NULL| NULL| |FX17500 | 143| 102547| M7544| 4512241| |FX17500 | 143| 458790| M7544| 4512364|
The information I want to move into tblHW comes from the tbl Pull_Down. Here is the setup:
|Name | Run_ID | Part1| SN1 | Attribute1_7| |AN 21-919G| Oct 08, 2013 / 100845| BOD| NA| 3RD U| |FR 55-013A| Oct 17, 2013 / 100853| Pmp| 2EA3A022| 78| |FR 55-013A| Oct 01, 2014 / 101383| Cbl| N/A| REDALEAD| |FR 43-223J| Apr 03, 2013 / 100594| BOD| NA| 3RD U| |VH 204 | May 17, 2014 / 101145| BOD| 3RD U| |Part2| SN2 | Attribute2_7| Part3 | SN3 | Attribute3_7| |Pmp | 2EA3F379| 78| Pmp| 2EA3N380| 117| |Pmp | 2EA3C020| 117| Pmp| 2EA3Y021| 117| |MLE | J14312161| 120| BOD| N/A| 3RD U| |Other| NA| Pmp| 2EA2X774| 78| |BOD | NULL| Pmp| 2EA4F075| 38|
A bit more information. I am receiving this information in the form of five excel spreadhsheets each with over 400 columns. The columns giving me the biggest headache are the 20 part columns that I need to place into the SQL table.
I need to somehow move each row into the tblHW but need to do something like this:
The first row AN 21-919G needs to have SN1 to be inserted into sn_mtr1 since it is a BOD, SN2 into SN_pmp1 since it is a PMP, and SN3 into sn_pmp2 since it is the second PMP here. I also need to get the pmp count, in this case 2 and then add the attribute1_7 and attribute2_7 to put into numstages when the prts are PMP.