Insert DataTable into SQL Server database table

Today in this article we will see a simple approach to Insert Data Table into SQL Server databases Efficiently and Faster way.

You may want to insert single or multiple data tables efficiently into an SQL Database.

Inserting data from the Datatable can be optimized using SQL Server’s Table-Valued Parameters (TVPs) combined with a custom user-defined table type for each DataTable.

However, if you are interested in inserting the Datatable records without using Table-Valued Parameters (TVPs) or stored procedures(SP) then it can be achieved using parameterized SQL queries which are explained below in detail.

Please install System.Data.SqlClient Nuget package as explained in this article to use ADO.NET features in your application where class SqlClient is defined.

• Connect SQL Server Database C# Example

Insert DataTable into SQL – Using Table Valued Parameters (TVPs)

For each of your DataTables, create a user-defined table type in SQL. Please make sure the table type matches the structure of the source DataTables.

This is the most efficient way of inserting the Table into the SQL server Database.

Please use SQL Server Management Studio or SQL scripts to achieve the same. (Ensure to have process edit access to your DB).

Example

CREATE TYPE EmployeeTypeA AS TABLE
(
        ID INT,
        NAME VARCHAR(25),
        CITY VARCHAR(50),
	COUNTRY VARCHAR(50)
);



CREATE TYPE EmployeeTypeB AS TABLE
(
        ID INT,
        NAME VARCHAR(25),
	CITY VARCHAR(50),
	COUNTRY VARCHAR(50)

);

Sample code to execute ADO.NET core for two or multiple tables using the TVP approach.

In the above code, we performed the steps in the below order.

1. We have two table employeeTableA and employeeTableB, that match the structures of EmployeeTypeA and EmployeeTypeB.
2. We establish a SqlConnection to your SQL Server.
3. We create two SqlCommands that call stored procedures, one for each table type, which use TVPs for insertion. These stored procedures should insert data from the TVPs into the respective target tables.
4. For each SqlCommand, we create a SqlParameter for the TVP, specifying the parameter name, SqlDbType (SqlDbType.Structured), and the TVP’s type name.
5. We execute the stored procedures for both EmployeeTypeA and EmployeeTypeB, which will insert the data from the DataTables into the respective tables.

Insert DataTable into SQL – Without TVP or Store Procedure (SqlBulkCopy)

If you are looking for other options like Inserting DataTable into SQL – Without TVP or Store Procedure then there is a simple approach of using SqlBulkCopy.

Synchronous SqlBulkCopy for Bulk insert

Here is a complete sample code for the same schema discussed above,

In the above code, we performed the steps in the below order.

1. We have two source tables employeeTableA and employeeTableB which needs to be inserted into SQL
2. We establish a SqlConnection to your SQL Server.
3. We consider using SQL transactions to ensure that either all or none of the data from the DataTables is inserted. If everything goes well, then only you commit the transaction.
4. For each DataTable, you create parameterized SQL queries and use SqlBulkCopy to insert the data.
5. Since using transaction, commit it to make the changes as final commit.
6. Close the SqlConnection when you’re done.

Additionally, to handle any exception please make sure to use try-and-catch blocks,

                    try
                    {
                      
                        bulkCopy.WriteToServer(employeeTableA );
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                       
                        connection.Close();
                    }

While using SqlBulkCopy it’s recommended to use SQL transactions to ensure that either all or none of the data from the DataTables is inserted.

This approach is reasonably efficient and one of other best option along with TRV.

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---