How to bulk insert an object with dynamic length and no column names

Question

I consume survey data (delivered as json) from our provider via SSE and deserialize them into an Object. The object holds the following data without the header information, just the data ("1","1","Yes, I do")

|UserNumber  | Variable1   | Variable2    |
|------------|-------------|--------------|
| 1          |      1      |  Yes, I do   |
| 1          |      5      |  No, I do not|

The number of Users and the number of variables differ from survey to survey but at the moment the maximum is 500.000 and 350. The object looks as follows:

(1) User1
  (1) "4482359"
  (2) "12526"
  (3) "5"
  (4) ""
...    
(2) User1
  (1) "5847895"
  (2) "33568"
  (3) "6"
  (4) "2"
...

I store the data in an object and now I want to store the data in a SQL Table but I am not sure how to do that since my object differs in length (both dimesions). What I have acomplished already is to bring the data into .csv and use Bulk Insert (even though I got problems due to the "," separator)

    query.Append("USE Import BULK INSERT dbo.[Insert_Table] FROM")
    query.Append(" 'C:\Users\I\Desktop\Insert_Table.csv' ")
    query.Append("With (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")

But for this I have to loop over all the elements.

I have also read about SQLBulkCopy but for that I have to bring the data into Datatable and map this one with the SQL Table. But since I do not have columnames in the object I do not see how to use this. Could anyone give me a snippet of how to insert the object into my database?

What *do* you want to do with that data? How do you want to query them? At some point you *have* to create a table with the columns you need for querying. — Panagiotis Kanavos, Jan 16 '19 at 15:22
SQL Server 2016 and later support JSON data but that delays parsing for later. If you want to filter by `Variable1` that will have to appear as a column at some point. Using JSON would still help by allowing you to store one row per response with all the data in a single JSON field. — Panagiotis Kanavos, Jan 16 '19 at 15:24
I just want to put the data into one table in my sql- server. There I want to do some data analysis. I get the header information separately vis a RestAPI Method from the provider and programmed code that creates me a table with it. Now I just want to put the data into it without any header information. — ruedi, Jan 16 '19 at 15:26
This seems more like a no-sql approach. "Just store all this data in here and I'll find out later how to use it". Maybe store the JSON directly then? — EzLo, Jan 16 '19 at 15:27
`I just want to put the data into one table in my sql- server.` why? Why not put the files in a folder or S3/Azure blob bucket? Data is placed on a database server to make querying faster. It's that analysis that determines how your tables should look like. Your ETL process should take the responses and load the reporting tables. Without knowing that, one can only propose adding a JSON field with the response. — Panagiotis Kanavos, Jan 16 '19 at 15:29

score 0 · Answer 1 · answered Jan 16 '19 at 16:37

Crate a table like this

            DataTable dt = new DataTable();
            dt.Columns.Add("UserNumber", typeof(int));
            for(int i = 1; i <=10; i++)
            {
                dt.Columns.Add("Column" + i.ToString(), typeof(string));
            }

How to bulk insert an object with dynamic length and no column names

1 Answers1