Fastest way to insert bulk records into SQL Server DB using bulkCopy in VB.NET

Fastest way to insert bulk records into SQL Server DB using SqlBulkCopy in VB.NET There are multiple ways to insert bulk data into SQL server database table to increase better performance and reduce multiple database hits. Following example tested in 1) .NET Framework 2.0 and Visual Studio 2005 2) SQL Server 2005 and SQL Express 3) Microsoft Enterprises Library 3.0 for database operation 1. Passing XML string to Stored Procedure In this method you can combine your data into XML and pass this XML as a string to SQL server stored procedure, this way you can increase your database hit performance up to 70% instead of using Line By Line insert methods. Example: I am giving example for two records you can increase records in XML (no limit) Sample XML String <Employees> <Emp> <Empno>1000</Empno> <Ename>Ritesh</Ename> <Deptno>A91</Deptno> </Emp> <Emp> <Empno>2000</Empno> <Ename>Rajiv</Ename> <Deptno>B56</Deptno> </Emp> </Employees> SQL Server Database table "Employee" CREATE TABLE [dbo].[Employee]( [Empno] [int] NOT NULL, [Ename] [nvarchar](100) NULL, [Deptno] [nvarchar](100) NULL ) ON [PRIMARY] END GO SQL Server Stored Procedure ALTER PROCEDURE ADDXML_EMP ( @prmEmployee XML, @prmOutStatus INT OUT ) AS DECLARE @XMLDocPointer INT EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @prmEmployee BEGIN SET NOCOUNT ON INSERT INTO Employee ( [Empno], [Ename], [Deptno] ) SELECT Empno, Ename, Deptno FROM OPENXML(@XMLDocPointer,'/Employees/Emp',2) WITH ( Empno integer, Ename nvarchar(100), Deptno nvarchar(100) ) SET @prmOutStatus = @@Error RETURN @@Error END VB.NET Code to execute stored procedure Namespace Imports Microsoft.Practices.EnterpriseLibrary.Data Imports System.Data.SqlClient Imports System.Xml Imports System.Data.Common Dim XMLString As String 'USE GetXML method for XMLString XMLString = "<Employees><Emp><Empno>1000</Empno><Ename>Ritesh </Ename><Deptno>A91</Deptno></Emp><Emp><Empno>2000 </Empno><Ename>Rajiv</Ename><Deptno>B56</Deptno></Emp> </Employees>" 'CreateDatabase method will create database object based on the connection name Dim connectDB As Database = ConfigDatabaseFactory.CreateDatabase("SQLServerDBConnectionString") Dim command As DbCommand command = connectDB.GetStoredProcCommand("AddXML_EMP") connectDB.AddInParameter(command, "@prmEmployee", DbType.Xml, XMLString) connectDB.AddOutParameter(command, "@prmOutStatus", DbType.Int32, 0) connectDB.ExecuteNonQuery(command) Note: If you are reading XML from file then constructed your string using following lines of code Dim xmlFilePath As String = "C:\Employees.xml" Dim xmlDoc As New XmlDocument xmlDoc.Load(xmlFilePath) Dim XMLString As String = xmlDoc.OuterXml.ToString() Data Inserted into Database table Empno Ename Deptno 1000 Ritesh A91 2000 Rajiv B56 Drawback XML methods is very reliable and fast performing method but If you have any special character in your data like notes field in database table column then this method will give error saying "Invalid character in XML" or if you have Image in the column then XML would help, to over come this another method to use SqlBulkCopy Note: If you want to do above method for Oracle Stored procedure the see below link. http://riteshk.blogspot.com/2008/09/sending-xml-string-as-input-parameter.html 2. Using "SqlBulkCopy" method to insert data into Database table 'Fill this data from any database like Oracle or SQL Server 'using some query like "Select * from Employee" Dim dsServerData As New DataSet 'Create Databse Dim localConnectDB As Database = ConfigDatabaseFactory.CreateDatabase("DBConString") 'Open Connection Using localConnection As DbConnection = localConnectDB.CreateConnection() 'Open Connection localConnection.Open() 'Create Transaction if you want Using transaction As DbTransaction = localConnection.BeginTransaction() 'Create SqlBulkCopy Using localCopy As New SqlBulkCopy(localConnection, SqlBulkCopyOptions.TableLock, transaction) Try 'loop if you have multiple table in a dataset For tableCount As Integer = 0 To dsServerData.Tables.Count - 1 'Define the BatchSize , it can be configurable too localCopy.BatchSize = dsServerData.Tables(tableCount).Rows.Count 'Define the table name where you want to insert data 'if you have exact table name in dataset then use 'localCopy.DestinationTableName = dsServerData.Tables(tableCount).TableName localCopy.DestinationTableName = "Employee" 'This "WriteToServer" will insert your complete table data into database table localCopy.WriteToServer(dsServerData.Tables(tableCount)) Next transaction.Commit() Catch ex As Exception transaction.Rollback() Finally 'Close Connection and bulkCopy localCopy.Close() localConnection.Close() End Try End Using End Using End Using Note: This method inserts 0.28 minutes to insert 1 million records in database Some other methods to load huge TextFile into SQL server database table http://blog.stevienova.com/2008/01/16/net-fastest-way-to-load-text-file-to-sql-sqlbulkcopy/