I have been working on a project recently that requires to insert a large amount of records from an Oracle database into a SQL Server 2005. I can’t use DTS to transfer the data because the data has to be extensively formatted before being inserted into the SQL Server.
My initial thought was to build a long SQL query by concatenating multiple INSERT statements, then execute the query to have the records to be inserted in a batch mode. However, it turned out not a efficient way because the number of records needs to be inserted is around 50,000 and I need to loop around 50,000 times to build the big string. Also, the data needs to be inserted into two tables with a relationship and I need to make sure the insert operation on the two table is in a transaction, so if anything happens, the database can be rolled back to its original state. My initial plan obviously can’t meet this requirement.
After some search, I found that in SQL Server 2005 I can pass an XML data to a stored procedure where the XML data can be queried for the insert operation.
Anyway, long storey short. First, let’s see how to query the XML data.
1. Query XML data in stored procedure
Note: ErrorHandler is just a procedure to roll back the changes in case that anything goes wrong.
2. Generate XML data on the fly
DataTable has a nice function called WriteXml which can generate a well-formatted XML based on the schema of the DtatTable. Below is the code snippet I used in my code to generate the XML data which is then passed as a parameter to the above stored procedure:
1: Dim dt As Customer.CustomerDataTable2: Dim sw As New StringWriter()3:4: Try5: dt = CustomerBLL.GetCustomer()6: dt.WriteXml(sw, False)7: Dim xmlData As String = sw.ToString()8: CustomersBLL.ImportCustomers(xmlData)9: Catch ex As Exception10: SendExceptionEmail(ex)11: Finally12: sw.Close()13: dt = Nothing14: End Try
At line 5, CustomerBLL is a class in my business logic layer and it has several shared (static in C#) functions to manipulate data. For example, ImportCustomer() looks like this:1: Public Shared Sub ImportCustomers(ByVal customerXMLData As String)2: Dim strConn As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString3: Dim conn As SqlConnection = New SqlConnection(strConn)4: Dim cmd As SqlCommand = New SqlCommand()5: cmd.Connection = conn6: cmd.CommandText = "sp_importCurrentCustomers"7: cmd.CommandType = CommandType.StoredProcedure8: cmd.Parameters.AddWithValue("@data", customerXMLData)9: cmd.CommandTimeout = 60010: Try11: conn.Open()12: cmd.ExecuteNonQuery()13: Catch ex As Exception14: Throw ex15: Finally16: conn.Close()17: End Try18: End Sub
Note: I set CommandTimeOut = 600 (10 minutes) to prevent query execution time-out error because of the large number of records being inserted.