User defined table type with dynamic columns in SQL Server

Question

In my .NET (C#) win application, I import an Excel file that contains multiple columns of varying numbers. So, I store the data in my datatable in code and I need to pass that datatable to a SQL Server stored procedure for further processing.

Is there any proper way to pass my datatable to a SQL Server stored procedure and can user defined table type have a dynamic number of columns?

you can create a global temp table and access it from the stored procedure. — Serkan Arslan, Nov 21 '19 at 05:52
@SerkanArslan can you please share more details about global temp table and how do i access. Thanks — Syed Wahhab, Nov 21 '19 at 06:00

score 1 · Answer 1 · answered Nov 21 '19 at 05:54

1

if it's possible to create a table type with a dynamic number of columns.

No, its not possible.

My suggestion would be you can update the data from datatable directly to sql server table like this, then use the table in your procedure.

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

Note, datatable column name should be same as your sql table column name to get it mapped properly.

answered Nov 21 '19 at 05:54

Pரதீப்

91,748
19
131
172

My problem is i dont have fixed columns, my columns can be differ each time i import excel. – Syed Wahhab Nov 21 '19 at 06:01
@SyedWahhab - Do you know all possible column names ? Each time it could be different columns present in excel, but do you have the full list of possible columns – Pரதீப் Nov 21 '19 at 06:05
I have a property data and their borrowers can be multiple one property may have Two borrowers and other property may have 15 borrowers, lets assume a maximum number of borrowers (50) at a single property, should i create a 50 columns and then utilize as per availability of data? PS: I cannot pass data in comma separated values for multiple column – Syed Wahhab Nov 21 '19 at 06:11
@SyedWahhab - Yeah, thats the idea. – Pரதீப் Nov 21 '19 at 06:16
According to me, you should use JSON to store data in database. You don't need to create multiple columns for borrowers. Create a column in table which will be of size max and store all your borrowers in JSON format in that column wrt property. – Prajakta Kale Nov 21 '19 at 06:38

score 0 · Answer 2 · edited Nov 21 '19 at 07:18

First of all, you can not create a table with a dynamic number of columns.

You can use JSON to store all your borrowers in a single column. You really don't need to create multiple columns for borrowers. You can simply create a column in a table that will be of size NVARCHAR(MAX) and you have to create a JSON of all borrowers and store it in a table.

While retrieving data from the table deserialize it to get in original form.

User defined table type with dynamic columns in SQL Server

2 Answers2