Creating a SQL Server table from a C# datatable

Question

I have a DataTable that I manually created and loaded with data using C#.

What would be the most efficient way to create a table in SQL Server 2005 that uses the columns and data in the DataTable?

Answer 1

If you mean from any arbitrary ADO.Net DataTable, I think you'd have to code that as a DDL 'code generation' tool, iterating through the DataTables' columns collection as you construct the "Create Table... " DDL Statement.

Then connect to the desired database and execute the constructed Create Table DDL statement.

Answer 2

I would just build a Create Table statement based on the DataTable and send that to Database. You can use SMO (SQL Server Managment objects) as well. Not sure what would be the fastest.

This is definitely something that could go into a framework level class for reuse.

The following link contains information (and code sample) on how to do this.

Creating a new table in SQL Server from ADO.NET DataTable

Hope that helps.

Answer 3

It's a little bit unusual in SQL to create tables out of a client supplied definition of a Datatable object. Tables are carefully crafted entities in SQL, with deploy time placement consideration of choosing the proper disk, with indexing consideration at design time and with all the issues involved in properly modeling a database.

Better you'd explain what you're trying to achieve so we understand what advice to give.

As a side note, in SQL 2008 there is a very easy way to create a table out of a client defined Datatable: pass the DataTable as a Table value parameter, then issue a SELECT * INTO <tablename> FROM @tvp, this will effectively transfer the definition of the Datatable and its content data into a real table in SQL.

Answer 4

How efficient do you need? I would probably write my own TSQL (based on the DataTable's columns) to create the table + columns, but to fill it you have a choice; if you have a moderate number of rows, a SqlDataAdapter should be fine. If you have lots of data, then SqlBulkCopy accepts a DataTable and table name...