Hi everyone,
We have a table with around 60-70k records that needs to be inserted from an Access table into an SQL Server Azure table.
Doing this with regular inserts takes several hours. So my idea was to make a stored procedure and submit many lines at the same time.
This worked just fine with an old Non Azure SQL server, but with the new Azure SQL Server, most lines go missing on the way.
We used something like this:
This sub was triggered by another Sub (simplified version):
The above code ran in under 10 minutes on the non-Azure SQL server, but when I try the same thing on Azure it only added around 12k out of 67k records. Every time it would run it would be a different number of records, so most records would not be inserted.
So after doing some Googling I tried a sub like the one below. It works, but it took nearly 2 hours for some reason. For every 25 rows it executes the stored procedure with 25 lines. If I run it with say a few hundred lines, some lines go missing for some reason, even if the stored procedure is only triggered once.
Does anyone know of any way to do this in a more efficient manner while making sure no records go missing?
Thank you
We have a table with around 60-70k records that needs to be inserted from an Access table into an SQL Server Azure table.
Doing this with regular inserts takes several hours. So my idea was to make a stored procedure and submit many lines at the same time.
This worked just fine with an old Non Azure SQL server, but with the new Azure SQL Server, most lines go missing on the way.
We used something like this:
Code:
Public Sub AddNewEntries(SQL As String)
Dim cmd As Object
Set cmd = New ADODB.Command
cmd.ActiveConnection = [connection string]
cmd.CommandType = adCmdText
cmd.CommandText = SQL
cmd.Execute
Set cmd = Nothing
End Sub
This sub was triggered by another Sub (simplified version):
Code:
Public Sub AddData()
c = 0
Dim rs As Object
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [sourcetable]")
SQL = ""
While Not rs.EOF
SQL = SQL & "EXEC [dbo].[StoredProc1] (all the fields and data taken from the rs recordset go here and [dbo].[StoredProc1] is the name of the stored procedure);" & vbCrLf
rs.MoveNext
c = c + 1
If c Mod 200 = 0 Then
AddNewEntries (SQL)
SQL = ""
DoEvents
ElseIf rs.EOF Then
AddNewEntries (SQL)
End If
Wend
rs.Close
Set rs = Nothing
The above code ran in under 10 minutes on the non-Azure SQL server, but when I try the same thing on Azure it only added around 12k out of 67k records. Every time it would run it would be a different number of records, so most records would not be inserted.
So after doing some Googling I tried a sub like the one below. It works, but it took nearly 2 hours for some reason. For every 25 rows it executes the stored procedure with 25 lines. If I run it with say a few hundred lines, some lines go missing for some reason, even if the stored procedure is only triggered once.
Code:
Public Sub AddDataV2()
Dim Cn As ADODB.Connection
Dim c As Long
c = 0
Dim rs As Object
Set Cn = New ADODB.Connection
Cn.Open [Connectiong string]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [sourcetable]")
SQL = ""
While Not rs.EOF
SQL = SQL & "EXEC [dbo].[StoredProc1] (all the field names and data go here);"& vbCrLf
rs.MoveNext
c = c + 1
If c Mod 25 = 0 Then
Cn.Execute SQL, , adAsyncExecute
Do While Cn.State = adStateOpen + adStateExecuting
Sleep 10 ' 10 ms delay
Loop
SQL = ""
DoEvents
ElseIf rs.EOF Then
Cn.Execute SQL, , adAsyncExecute
End If
Wend
rs.Close
Set rs = Nothing
Set Cn = Nothing
End Sub
Does anyone know of any way to do this in a more efficient manner while making sure no records go missing?
Thank you