Very Large Insert

[sebs]

Hey Guys,

I have to populate an MS SQL database for a client, he has a database (.dbf) that his store uses and I am writing an app that is reading all of those record and insert them in a MS SQL database for his online store.

There's around 50 000 record and so far I open his .dbf database, go thru all the record and thought that it would be better to build a file with all my insert in it instead of doing it every loop.

But when it commes time to send it to the sql database, it takes very long and it looks like it hangs.

Here's my function that updates the database:

Private Sub updateWebDatabase()
  Dim sqlStatement As String
  Dim fso As New FileSystemObject
  Dim fle As TextStream
  
  lblProgress.Caption = "Saving to Web Database"
  lblProgress.Refresh
  
  Set fle = fso.OpenTextFile(sqlTxtPath)
  
  sqlStatement = fle.ReadAll
  
  Set fle = Nothing
  Set fso = Nothing
  
  Call openDB("MsSQL")
    Set rs = conn.Execute(sqlStatement)
  Call closeDB
End Sub

Is there a way to submit my file to the sql database or is there a better way than sending it 50 000 insert's ?

Thanks a lot