SQL BulkCopy vs OPENDATASOURCE which one is faster

.net c# sqlbulkcopy sql-server

Question

I want to insert data into sqlserver from access .mdb file, which is the fastest way to perform the data inserts SQL BulkCopy or OPENDATASOURCE using OLEDB.

Data would be like 100,000s of records for about 20 tables.

Thanks.

I am doing this from c# code,where i need which one performs better,I am not able to compare because OPENDATASOURCE is asking for admin permissions.

1
2
7/8/2013 12:10:33 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

In my Opinion the very best insert performance for inserting large volumes of data can be attained by using SQL Bulk Copy as this method bypasses the SQL query language entirely and streams data directly to SQL.

Further Performance Improvements

  • By defaults SqlBulkCopy uses row locks during the bulk copy operation. If TableLock option in the SqlBulkCopyOptions is specified, it obtains a bulk update lock for the duration of the bulk copy operation and performance will be improved.
  • Clustered Table: if it is feasible, the best way to load it is to remove the clustered index.
  • Indexes: Indexes create huge problems with the parallelism so is it always a good idea to load without any indexes active

You may read more on Whitepaper: performance of SqlBulkCopy http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf

1
7/8/2013 12:45:12 PM

Related Questions

Is it possible to recover PrimayKey IDs after a SQL BulkCopy?

Disclaimer...: I'm the owner of the project ...C# Bulk Operations...The library overcome SqlBulkCopy limitations and add flexible features like output inserted identity value....Behind the code, it does exactly like the accepted answer but way easier to use....var bulk = new BulkOperation(connection); // Output Identity bulk.ColumnMappings.Add("Pr...
c# sql sqlbulkcopy sql-server-2005
asked by Jonathan Magnan

Using sp dboption, you can't allow select into/bulkcopy on a SQL Server 2005 database.

I am trying to enable the select into/bulkcopy database option on a SQL Server 2005 database using the command:...EXEC sp_dboption 'mydbname', 'select into/bulkcopy', 'true' ...After executing the above, running ...EXEC sp_dboption 'mydbname', 'select into/bulkcopy'... tells me that the option is still set to OFF. ...I've confirmed that my Windows ...
database-permissions sqlbulkcopy sql-server sql-server-2005
asked by Jason Sparks

Is there a BulkUpdate command in SQL Server 2008 that is equivalent to BulkCopy?

I have used ...BulkCopy... command to transfer rows from one table to another table with bulk data about 3 to 5 million rows. I want to update these rows....Is there any ...BulkUpdate... command similar to the ...BulkCopy... command? I'm using ASP.NET with C#.
c# sqlbulkcopy sql-server sql-update
asked by SHEKHAR SHETE

SQL Server import faster than bulk import

I have just spent the last few weeks fighting with the optimizing a very large load myself. BULK INSERT is the fastest way, I found with BCP, as opposed to SSIS or TSQL Bulk Insert, but there are things you can do to tune this. ...Try raising/lowering the rows per batch setting, to move resource pressure between CPU and memory (higher will reduce C...
bulkinsert sqlbulkcopy sql-server
asked by Pete Carter

C# BulkCopy with SQL Server Compact Edition, is it possible?

You can use my SqlCeBulkCopy library, available via NuGet for SQL CE 4.0, or otherwise available here: ...https://sqlcebulkcopy.codeplex.com/
c# sqlbulkcopy sql-server-ce
asked by ErikEJ

how to use sql Bulkcopy to insert datagridview rows in table

i want insert datagridview rows into sql server tables ... and now used this code to insert rows values from DataGridView in table :...private DataTable GetDataTableFromDGV(DataGridView dgv) { var dt = new DataTable(); foreach (DataGridViewColumn column in dgv.Columns) { if (column.Visible...
c# datagridview sql sqlbulkcopy
asked by ghasem deh

.Net datetime with milliseconds sql server using bulkcopy thows exception

I have this code, which throws exception....SqlConnection con; con = new SqlConnection(connectionStr); SqlCommand cmd = con.CreateCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = @"select [ID], [VERSION_ID], '08/26/2015 09:33:24:717 AM' as [Added_Dt], [Loc_ID] as Column1 from dbo.Source where Added_Dt between CO...
.net c# sql sqlbulkcopy sql-server-2005
asked by digitally_inspired

INSERT object or UPDATE if it already exists using BulkCopy (C#, SQL)

I'm trying to update a table with a ...List<T>.... I created an EnumExtension class and created a method which converts my Enumerable to a DataTable:...public static DataTable AsDataTable<T>(this IEnumerable<T> data)... ...And then I started creating a method which should use BulkCopy to insert or update my table, using the enumerable. Right now, i...
c# sqlbulkcopy sql-server
asked by Lucas Garcia

How Transform OracleAdapter Dataset to SQL BulkCopy

I've successfully used the ...Oracle.ManagedDataAccess.Client... to fetch data from an Oracle server into a ...Dataset..., and used ...SqlBulkCopy... to insert that ...Dataset... into a SQL database where the tables have matching columns names (see below)....How would I go about manipulating the ...Insert... of the ...Dataset... so I can specify w...
dataset oracle oracle-manageddataaccess sqlbulkcopy vb.net
asked by Wowdude

Bulkcopy 100 million rows of data to Azure SQL Server using C#?

I have a local SQL Server 2017 database, and I need to copy two tables to an Azure SQL Server database; one table has over 100 million rows of data including a "geography" type column. How do I do that? ...I am right now running a bulk copy:...using (SqlConnection streamsConnection = new SqlConnection(streamsConnectionString)) { streamsConnecti...
c# sqlbulkcopy sql-server-2017
asked by Johan Grobler

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow