Batch insert to SQL Server table from DataTable using ODBC Connection

Question

I have been asked to look at finding the most efficient way to take a DataTable input and write it to a SQL Server table using C#. The snag is that the solution must use ODBC Connections throughout, this rules out sqlBulkCopy. The solution must also work on all SQL Server versions back to SQL Server 2008 R2.

I am thinking that the best approach would be to use batch inserts of 1000 rows at a time using the following SQL syntax:

INSERT INTO dbo.Table1(Field1, Field2) SELECT Value1, Value2 UNION SELECT Value1, Value2

I have already written the code the check if a table corresponding to the DataTable input already exists on the SQL Server and to create one if it doesn't.

I have also written the code to create the INSERT statement itself. What I am struggling with is how to dynamically build the SELECT statements from the rows in the data table. How can I access the values in the rows to build my SELECT statement? I think I will also need to check the data type of each column in order to determine whether the values need to be enclosed in single quotes (') or not.

Here is my current code:

        public bool CopyDataTable(DataTable sourceTable, OdbcConnection targetConn, string targetTable)
    {
        OdbcTransaction tran = null;
        string[] selectStatement = new string[sourceTable.Rows.Count];

        // Check if targetTable exists, create it if it doesn't
        if (!TableExists(targetConn, targetTable))
        {
            bool created = CreateTableFromDataTable(targetConn, sourceTable);

            if (!created)
                return false;
        }

        try
        {
            // Prepare insert statement based on sourceTable
            string insertStatement = string.Format("INSERT INTO [dbo].[{0}] (", targetTable);

            foreach (DataColumn dataColumn in sourceTable.Columns)
            {
                insertStatement += dataColumn + ",";
            }

            insertStatement += insertStatement.TrimEnd(',') + ") ";

            // Open connection to target db
            using (targetConn)
            {
                if (targetConn.State != ConnectionState.Open)
                    targetConn.Open();

                tran = targetConn.BeginTransaction();

                for (int i = 0; i < sourceTable.Rows.Count; i++)
                {
                    DataRow row = sourceTable.Rows[i];

                    // Need to iterate through columns in row, getting values and data types and building a SELECT statement

                    selectStatement[i] = "SELECT ";
                }

                insertStatement += string.Join(" UNION ", selectStatement);

                using (OdbcCommand cmd = new OdbcCommand(insertStatement, targetConn, tran))
                {
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();
                return true;
            }
        }       
        catch 
        {
            tran.Rollback();
            return false;
        }
    }

Any advice would be much appreciated. Also if there is a simpler approach than the one I am suggesting then any details of that would be great.

Are you also ruling out ADO.net Table parameters ? That's a weird restriction since passing in a Table parameter to the stored procedure and using an MERGE command on the incoming table is by far the most efficient way to achieve this. — PhillipH
– PhillipH, Commented Aug 18, 2016 at 8:45
The problem is that we will have very limited rights on the SQL Servers we are writing to. We have CRUD on tables and that's about it. Any solution involving stored procs and/or TVP's is not in scope I'm afraid. — Chris D King
– Chris D King, Commented Aug 18, 2016 at 9:15

PhillipH · Accepted Answer · 2016-08-18 10:05:55Z

2

Ok since we cannot use stored procedures or Bulk Copy ; when I modelled the various approaches a couple of years ago, the key determinant to performance was the number of calls to the server. So batching a set of MERGE or INSERT statements into a single call separated by semi-colons was found to be the fastest method. I ended up batching my SQL statements. I think the max size of a SQL statement was 32k so I chopped up my batch into units of that size.

(Note - use StringBuilder instead of concatenating strings manually - it has a beneficial effect on performance)

Psuedo-code
string sqlStatement = "INSERT INTO Tab1 VALUES {0},{1},{2}";
StringBuilder sqlBatch = new StringBuilder();
foreach(DataRow row in myDataTable)
{
    sqlBatch.AppendLine(string.Format(sqlStatement, row["Field1"], row["Field2"], row["Field3"]));
    sqlBatch.Append(";");
}
myOdbcConnection.ExecuteSql(sqlBatch.ToString());

You need to deal with batch size complications, and formatting of the correct field data types in the string-replace step, but otherwise this will be the best performance.

edited Aug 18, 2016 at 10:05

answered Aug 18, 2016 at 9:26

PhillipH

6,2221 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris D King Over a year ago

Are you able to share the code you used to build the SQL statements in the batches? That's where I'm currently stuck.

VBWebProfi · Accepted Answer · 2017-04-09 18:30:46Z

1

Marked solution of PhillipH is open for several mistakes and SQL injection.

Normally you should build a DbCommand with parameters and execute this instead of executing a self build SQL statement.

The CommandText must be "INSERT INTO Tab1 VALUES ?,?,?" for ODBC and OLEDB, SqlClient needs named parameters ("@<Name>").

Parameters should be added with the dimensions of underlaying column.

answered Apr 9, 2017 at 18:30

VBWebProfi

4094 silver badges6 bronze badges

Collectives™ on Stack Overflow

Batch insert to SQL Server table from DataTable using ODBC Connection

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related