sql bulk copy method to insert large amount of data to the sql database - codeproject

12/24/13 SQL Bulk copy method to insert large amount of data to the sql database - CodeProject

www.codeproject.com/Tips/309564/SQL-Bulk-copy-method-to-insert-large-amount-of-dat 1/4

10,284,208 members (59,050 online)

Sign in

home quick answers discussions features community help Search for articles, questions, tips

Articles Database Database General

Tip

Browse Code

Stats

Revisions (2)

Alternatives (1)

Comments &Discussions (11)

About Article

Fastest and most efficientmethod to insert largeamount of records to a SQLServer database from oursystem generated datatable.

Type Tip/Trick

Licence CPOL

First Posted 3 Jan 2012

Views 39,485

Bookmarked 20 times

SQL VB.NET insert

Top News

Microsoft bets on Windows

XP disaster

Get the Insider News free eachmorning.

Related Videos

Related Articles

SQL Bulk copy method to insertlarge amount of data to the sqldatabase

Handling BULK Data insert fromCSV to SQL Server

SQL Bulk Copy with C#.Net

Oracle to SQL Server VB.NETUtility application

Next

Sign Up to vote

5Tweet

SQL Bulk copy method to insert large amount of data

to the sql databaseBy bluesathish, 3 Jan 2012

I was recently tasked with a project at a company to update an SQL Server 2008 database with large amounts of

data each day. The task at first seemed daunting due to the files exceeding well over 400,000 records and there

were several that needed processing daily. I first tried LINQ to SQL, but with the amount of data, the inserts

were slow performing to say the least. Then I remembered the SqlBulkCopy class. SqlBulkCopy lets you

efficiently bulk load a SQL Server table with data from another source. The SqlBulkCopy class can be used to

write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can

be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. For

this example the file will contain roughly 1000 records, but this code can handle large amounts of data.

To begin with lets create a table in SQL Server that will hold the data. Copy the following T-SQL into SQL Server

to create your table:

Collapse | Copy Code

CREATE TABLE [dbo].[Censis]( [Suburb] [varchar](200) NULL, [NotStated] [int] NULL, [NotApplicable] [int] NULL, [Fishing] [int] NULL, [Mining] [int] NULL, [Manufacturing] [int] NULL, [Electricity] [int] NULL, [Construction] [int] NULL) ON [PRIMARY]GO

The table above will hold Census data that is freely available to download in Australia.

The next item to do is create a console application that will bulk load the data. Open Visual Studio 2008 and

choose File > New > Windows > Console Application.

Before moving on, to explain the code I have to work backwards and explain the final method that bulk loads

data. SqlBulkCopy has a method called WriteToServer. One of the overloads of this method takes a

DataTable as the parameter. Because a DataTable contains rows and columns, this seemed like a logical

choice for the task I was facing.

Jumping back to the example we now know we need to create a DataTable that contains the information from

the text file. The code below demonstrates how to do this:


DataTable dt = new DataTable();string line = null;int i = 0; using (StreamReader sr = File.OpenText(@"c:\temp\table1.csv")){ while ((line = sr.ReadLine()) != null) { string[] data = line.Split(','); if (data.Length > 0) { if (i == 0) { foreach (var item in data)

5.00 (5 votes)

articles



How To Store Any File into SQL

Database

Using SQL bulk copy with yourLINQ-to-SQL datacontext

Retrieving failed records after an

SqlBulkCopy exception

SqlBulkCopy in ADO.NET 2.0,SqlBulkCopy Class in C#,

SqlBulkCopy help, SqlBulkCopyguide, SqlBulkCopy class,

SqlBulkCopy tutorial

Bulk CRUD Operation using

XQuery and Reflection inASP.NET

Using SqlBulkCopy with

ASP.NET 2.0

Transfer data or script betweentwo SQL Server databases

Very fast test data generation

using exponential INSERT

Bulk INSERT / UPDATE / DELETEin LINQ to SQL

Using SQL Bulk Copy with your

LINQ-to-Entities datacontext -Part 1

Generate SQL Insert statements

for your SQL Server 2000

Database

Database performanceoptimization part 2 (Indexmaintenance)

Generating INSERT statements

in SQL Server

SQL Server Interview Questions

and Answers Complete List

Download

SQL Server 2000 - Merge

Replication Step by Step

Procedure

Fast Exporting from DataSet to

Excel

{ dt.Columns.Add(new DataColumn()); } i++; } DataRow row = dt.NewRow(); row.ItemArray = data; dt.Rows.Add(row); } }}

VB.NET Collapse | Copy Code

Dim dt As New DataTable()Dim line As String = NothingDim i As Integer = 0 Using sr As StreamReader = File.OpenText("c:\temp\table1.csv") line = sr.ReadLine() Do While line IsNot Nothing Dim data() As String = line.Split(","c) If data.Length > 0 Then If i = 0 Then For Each item In data dt.Columns.Add(New DataColumn()) Next item i += 1 End If Dim row As DataRow = dt.NewRow() row.ItemArray = data dt.Rows.Add(row) End If line = sr.ReadLine() LoopEnd Using

In the code above, I created a DataTable that will store all the information from the csv file. The CSV file resides

in the C:\Temp directory. I am using a StreamReader object to open the file and read each line in the file. Each

line is then split up into a string array. That string array will be assigned to each DataRow as the ItemArray

value. This sets the values for the row through the array.

When the file has been read, the next thing to do is use the SqlBulkCopy class to insert the data into SQL

Server. The following code demonstrates how to do this:


using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings[ "ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString)){ cn.Open(); using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { copy.ColumnMappings.Add(0, 0); copy.ColumnMappings.Add(1, 1); copy.ColumnMappings.Add(2, 2); copy.ColumnMappings.Add(3, 3); copy.ColumnMappings.Add(4, 4); copy.DestinationTableName = "Censis"; copy.WriteToServer(dt); }}

VB.NET Collapse | Copy Code

Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_ "ConsoleApplication3.Properties.Settings.daasConnectionString").ConnectionString) cn.Open() Using copy As New SqlBulkCopy(cn) copy.ColumnMappings.Add(0, 0) copy.ColumnMappings.Add(1, 1) copy.ColumnMappings.Add(2, 2) copy.ColumnMappings.Add(3, 3) copy.ColumnMappings.Add(4, 4) copy.DestinationTableName = "Censis" copy.WriteToServer(dt) End UsingEnd Using

SqlBulkCopy uses ADO.NET to connect to a database to bulk load the data. I have created an SqlConnection

object, and that object reference is used to create the SqlBulkCopy object. The DestinationTableName

property references a table in the database where the data is to be loaded. A handy feature of SqlBulkCopy is



bluesathishSoftware Developer

India

No Biography provided

Search this forum Go

the SqlBulkCopyColumnMappingCollection. Column mappings define the relationships between columns in

the data source and columns in the destination. This is handy if the data source file has columns that dont need

to be inserted into the database. Column mappings can be set by an index, such as the example above, or they

can be set by the name of the column. Using the index is handy when youre working with files that contain no

column names. Make sure both of your datatable and sqltable columns are in the same order. Finally the data is

sent to the database by running the WriteToServer method.

Hence using sqibulkcopy() method is a very fastest than any other insertion method.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License

(CPOL)

About the Author

Article Top

Comments and Discussions

You must Sign In to use this message board.

Profile popups Spacing Relaxed Noise Very High Layout Normal Per page 10 Update

First Prev Next

Member 8331618 23-Jul-13 0:58

vishal_h 13-May-13 19:24

bluesathish 13-May-13 19:46

vishal_h 13-May-13 21:10

franco.fral 31-Jan-13 6:59

Sefavolon 26-Sep-12 3:43

bluesathish 26-Sep-12 3:56

Sefavolon 26-Sep-12 4:52

how about data type difference between data table

and destination table

What if data is large and getting

'System.OutOfMemoryException' ERROR

Re: What if data is large and getting


Re: What if data is large and getting


SqlBulkCopy from CSV to SQLServer with

uniqueidentifier datatype

for each item in data

Re: for each item in data

Re: for each item in data

sql bulk copy method to insert large amount of data to the sql database - codeproject

Documents