Mega Code Archive
Bulk loading data from one table to another database (VB)
<![CDATA[
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<script runat="server">
Sub btnBulkCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBulkCopy.Click
Dim YourDatabaseConString As String
Dim NorthWindConString As String
Dim YourDatabaseCon As SqlConnection = New SqlConnection()
Dim NorthwindCon As SqlConnection = New SqlConnection()
Dim YourDatabaseCom As SqlCommand = New SqlCommand()
Dim YourDatabaseReader As SqlDataReader
YourDatabaseConString = ConfigurationManager.ConnectionStrings("DSN_YourDatabase").ConnectionString
NorthWindConString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
YourDatabaseCon.ConnectionString = YourDatabaseConString
YourDatabaseCom.Connection = YourDatabaseCon
YourDatabaseCom.CommandText = " SELECT ID, First_Name, Last_Name, " & _
" 'YourDatabase' as Source FROM MailingList_Temp "
YourDatabaseCom.CommandType = CommandType.Text
YourDatabaseCom.Connection.Open()
Dim NorthWindBulkOp As SqlBulkCopy
NorthWindBulkOp = New SqlBulkCopy(NorthWindConString, _
SqlBulkCopyOptions.UseInternalTransaction)
NorthWindBulkOp.DestinationTableName = "Employees"
NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID")
NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName")
NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName")
Dim JobTitleColMap As SqlBulkCopyColumnMapping
JobTitleColMap = New SqlBulkCopyColumnMapping("Source", "Title")
NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap)
NorthWindBulkOp.BulkCopyTimeout = 500000000
AddHandler NorthWindBulkOp.SqlRowsCopied, _
New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
NorthWindBulkOp.NotifyAfter = 1000
YourDatabaseReader = YourDatabaseCom.ExecuteReader()
Try
NorthWindBulkOp.WriteToServer(YourDatabaseReader)
Catch ex As Exception
' Write error handling code here
lblResult.Text = ex.Message
Finally
YourDatabaseReader.Close()
End Try
End Sub
Private Sub OnSqlRowsCopied(ByVal sender As Object, _
ByVal args As SqlRowsCopiedEventArgs)
lblCounter.Text += args.RowsCopied.ToString() + " rows are copied<Br>"
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Bulk Loading Large Volume Data</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />
<br />
<asp:Label ID="lblResult" Runat="server"></asp:Label>
<br />
<asp:Label ID="lblCounter" Runat="server"></asp:Label>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<connectionStrings>
<add name="DSN_Northwind"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="DSN_YourDatabase"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True"
providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration>
]]>