I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I have a couple hundred entries and the CSV files are about 25 megabytes each.
Each of these rows contain a row label, a data type label, a start position, and an end position; so about 4 fields per row.
The row label id has over a million potential values (nor do I have a complete list of potential values), and will not appear in the same order in each record nor necessarily even appear.
The data type label is categorical.
The start and end position fields are large integers.
My thoughts are to create a table with Record names and supporting data, another with the categorical label of the data, but I am being challenged on how to deal with the 750,000 columns per entry and the start and end positions.
I was thinking of big data table with all 750,000 rows per entry. but it seems like it would be very inefficient to approach it this way; the table looking something like this.
I am running SQL Server 2012 standard edition.
What is the best practice schema to address this type of large data set?