temptable sqlbulkcopy dapper的Sql批量添加

        /// <summary>
        /// 用TemporaryTable + SqlBulkCopy + Dapper的方法实现批量插入
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        int InsertMany<T>(List<T> list, string tableName, string columnsStr, string connectionString)
        {
            if (string.IsNullOrWhiteSpace(tableName)
                || string.IsNullOrWhiteSpace(columnsStr)) { return 0; }
            var asTable = JsonConvert.DeserializeObject<DataTable>(JsonConvert.SerializeObject(list));
            asTable.AcceptChanges();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var trans = connection.BeginTransaction();
                var tempTableName = "#TempInsertMany";
                //生成临时表
                connection.Execute($@"SELECT TOP (0) {columnsStr} INTO [{tempTableName}] FROM {tableName}"
                   , null, trans);
                //把数据移到临时表
                using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans))
                {
                    bulkCopy.BulkCopyTimeout = (int)TimeSpan.FromSeconds(30).TotalSeconds;
                    bulkCopy.BatchSize = 100;
                    bulkCopy.DestinationTableName = tempTableName;
                    bulkCopy.WriteToServer(asTable.CreateDataReader());
                }
                //把临时表的数据添加到表中
                var rst = connection.Execute(
                    $@"INSERT INTO {tableName} ({columnsStr})
                    select {columnsStr} from {tempTableName}", transaction: trans);
                trans.Commit();
                return rst;
            }
        }

参考

Bulk Insert with Dapper