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 -
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
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
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
PIVOTed into your table's schema. Use this 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.