To be able to PIVOT
values into the shape of your target Parameter
table you need a set of (parameter_name, parameter_value) tuples. I assume the design is able to distinguish one parameter value from another, either because of its position in the file (row 1 is Parameter A etc.) or due to a label in the row (ParamA=ValueA etc.).
For a positional design, create a reference table with two columns - row_number
and parameter_name
and populate it accordingly. Create a staging table with an identity column and parameter_value
. Import the parameter rows from a single source file into this staging table. Values will be generated by the identity in the order that rows appear in the source file so the first parameter value will be ID=1 and the last will be ID=40. Join the staging and reference tables to get the tuple.
For the embedded labels use string functions to separate the name and value on the delimiter. This will be simpler with two staging tables - one with a single column to hold the raw records from file and the second with two columns to hold parameter_name
and parameter_value
separately.
I'd suggest you import each file in two steps. In step 1 bring in the 40 rows that represent your parameters. In step 2 bring in the remaining rows which are data. BULK INSERT
has FIRSTROW
and LASTROW
parameters which can control this. bcp utility has similar switches.
Now you have a set of 40 (parameter_name, parameter_value) tuples that can be PIVOT
ed into your table's schema. Use this to INSERT
to Parameter
table, which presumably will have a surrogate primary key which you can capture (an INSERT .. OUPUT
will help). Use this value in the BULK INSERT
of the remaining data points. This is why I suggest doing it in two steps.
Package the whole thing as a stored procedure. Use a Powershell script to itterate over your files and call the SP for each. You will need to use dynamic SQL for the BULK INSERT
. If you use temporary tables for the staging table you can run any number of processing streams in parallel without worrying about tidy-up.
Before each file is processed TRUNCATE
the staging table. This will reset the idenity as well as remove the previous file's values.
Of course the whole file could be read into a staging table and the parameters separated from sensor data afterwards. This works equally well, it just represents additional run-time work for little value in my opinion.