Efficient Bulk Insertion of DataTable into SQL Server Using User-Defined Table Types

Introduction

In modern enterprise applications, data import and synchronization are common business requirements. Particularly in human resource management and reporting systems, there is often a need to batch import employee weekly report data from Excel format into SQL Server databases. Traditional row-by-row insertion methods are inefficient when handling large volumes of data, while SQL Server's table-valued parameter technology provides an efficient solution for such scenarios.

Problem Analysis

From the Q&A data, we can see the core problem users face is how to efficiently insert DataTable data into SQL Server database tables. Specific requirements include: reading employee work hour reports from Excel files weekly and updating records in the database. This involves two key decisions: choosing between row-by-row insertion or batch operations, and using OLE objects or SQL Server native objects.

User-Defined Table Type Technology Detailed Explanation

Database-Side Configuration

First, create a user-defined table type in SQL Server that matches the DataTable structure:

CREATE TYPE [dbo].[EmployeeHoursType] AS TABLE(
    [EmployeeID] int NOT NULL,
    [WeekStartDate] date NOT NULL,
    [HoursWorked] decimal(5,2) NOT NULL,
    [Department] [nvarchar](128) NULL
)

This table type defines the basic structure of employee hour reports, including employee ID, week start date, hours worked, and department information.

Stored Procedure Design

Next, create a stored procedure that accepts table-valued parameters:

CREATE PROCEDURE [dbo].[InsertEmployeeHours]
    @hoursData EmployeeHoursType readonly
AS
BEGIN
    -- Direct insertion of all data
    INSERT INTO [dbo].[EmployeeWeeklyHours] 
    SELECT * FROM @hoursData
    
    -- Or perform data validation and transformation
    INSERT INTO [dbo].[EmployeeWeeklyHours] (EmployeeID, WeekStartDate, HoursWorked, Department)
    SELECT EmployeeID, WeekStartDate, HoursWorked, UPPER(Department)
    FROM @hoursData
    WHERE HoursWorked > 0
END

The stored procedure can include business logic such as data validation, transformation, and deduplication.

C# Client Implementation

In the C# application, pass the DataTable as a parameter to the stored procedure:

public void InsertEmployeeHours(DataTable hoursData)
{
    using (var connection = new SqlConnection(connectionString))
    using (var command = new SqlCommand("InsertEmployeeHours", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        
        var parameter = new SqlParameter("@hoursData", hoursData)
        {
            SqlDbType = SqlDbType.Structured,
            TypeName = "dbo.EmployeeHoursType"
        };
        
        command.Parameters.Add(parameter);
        
        connection.Open();
        command.ExecuteNonQuery();
    }
}

Technical Advantages Analysis

Performance Comparison

Compared with traditional row-by-row insertion, table-valued parameter technology offers significant advantages:

• Reduced Network Round Trips: Single call transmits all data, avoiding multiple database round trips
• Lower Transaction Overhead: All insertion operations handled within database, reducing transaction management overhead
• Optimized Lock Contention: Batch operations reduce table lock contention

Data Integrity Assurance

By implementing business logic in stored procedures, data consistency and integrity can be ensured:

CREATE PROCEDURE [dbo].[InsertEmployeeHoursWithValidation]
    @hoursData EmployeeHoursType readonly
AS
BEGIN
    BEGIN TRANSACTION
    
    BEGIN TRY
        -- Check for duplicate records
        IF EXISTS(SELECT 1 FROM @hoursData hd 
                 INNER JOIN dbo.EmployeeWeeklyHours ewh 
                 ON hd.EmployeeID = ewh.EmployeeID 
                 AND hd.WeekStartDate = ewh.WeekStartDate)
        BEGIN
            RAISERROR('Duplicate employee weekly report records exist', 16, 1)
        END
        
        -- Insert new records
        INSERT INTO dbo.EmployeeWeeklyHours
        SELECT * FROM @hoursData
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        THROW
    END CATCH
END

Alternative Solutions Comparison

SqlBulkCopy Method

SqlBulkCopy is another bulk insertion technology suitable for simple data migration scenarios:

public void BulkInsertEmployeeHours(DataTable hoursData)
{
    using (var connection = new SqlConnection(connectionString))
    using (var bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "EmployeeWeeklyHours";
        
        // Configure column mappings
        bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID");
        bulkCopy.ColumnMappings.Add("WeekStartDate", "WeekStartDate");
        bulkCopy.ColumnMappings.Add("HoursWorked", "HoursWorked");
        bulkCopy.ColumnMappings.Add("Department", "Department");
        
        connection.Open();
        bulkCopy.WriteToServer(hoursData);
    }
}

Method Selection Guide

Choose the appropriate insertion method based on specific requirements:

• Table-Valued Parameters: Use when complex business logic, data validation, and transaction control are needed
• SqlBulkCopy: Use for simple data migration with extremely high performance requirements
• Row-by-Row Insertion: Use for small data volumes requiring per-row error handling

Practical Application Scenario Optimization

Weekly Report Data Update Strategy

For the weekly update requirement of employee report data, the following strategy can be adopted:

CREATE PROCEDURE [dbo].[UpsertEmployeeHours]
    @hoursData EmployeeHoursType readonly
AS
BEGIN
    MERGE dbo.EmployeeWeeklyHours AS target
    USING @hoursData AS source
    ON target.EmployeeID = source.EmployeeID 
       AND target.WeekStartDate = source.WeekStartDate
    WHEN MATCHED THEN
        UPDATE SET 
            HoursWorked = source.HoursWorked,
            Department = source.Department
    WHEN NOT MATCHED THEN
        INSERT (EmployeeID, WeekStartDate, HoursWorked, Department)
        VALUES (source.EmployeeID, source.WeekStartDate, source.HoursWorked, source.Department);
END

Performance Tuning Recommendations

• Create appropriate indexes on target tables, particularly composite indexes on EmployeeID and WeekStartDate
• Adjust batch size appropriately when processing data in batches
• Monitor and optimize stored procedure execution plans

Conclusion

User-defined table type technology provides an efficient and reliable solution for bulk data insertion from DataTable to SQL Server. Through the combination of table-valued parameters and stored procedures, not only can high-performance data import be achieved, but data consistency and integrity can also be guaranteed at the database level. For scenarios involving weekly updates of employee report data, this technical solution is particularly suitable, meeting enterprises' dual requirements for data accuracy and processing efficiency.