none
Bulk Insert?

    Question

  • Is there a way to do some sort of a bulk insert into SQLCE Compact 3.5?  I have data I need moved from an Access 2000 database into a SQLCE Comact database and the regular Insert is taking forever.  Any ideas?

     

    TIA

    Monday, April 28, 2008 7:55 PM

Answers

  • I moved my Parameter.Add statements outside my loop and it significantly improved performance.  Thanks for the links!

    Tuesday, April 29, 2008 2:30 PM

All replies

  • There is no Bulk Insert equivalent in SQL Compact. You can either develop your own code to transfer data or use third party tools such as these.

     

    Monday, April 28, 2008 8:48 PM
  • I'm using VB.Net to create may SqlCeCommand Insert command.  Then I am looping through all my access records, setting my SqlCeParameters, and then using ExecuteNonQuery.  Perhaps I should post this to another forum...

    Monday, April 28, 2008 8:54 PM
  • That is one way to do it, but it's slow. You can try preparing your command and when possible create the command parameters stating their size (this seems to speed up things a bit). The fastest possible way to go about this is via a base table cursor and a class that is available only on mobile devices: SqlCeResultSet. To do this on the desktop you must use native code (AFAIK).

     

    Monday, April 28, 2008 9:28 PM
  • Here is what I am doing now.  It seems as if the add parameter is the slow part.

     

    'Access Database

    Dim sAccessSQL As String

    Dim objAccessConnection As Data.OleDb.OleDbConnection

    Dim objAccessDataAdapter As Data.OleDb.OleDbDataAdapter

    Dim objAccessCommandBuilder As Data.OleDb.OleDbCommandBuilder

    Dim sAccessConnection As String

    Dim objAccessDataSet As Data.DataSet

    Dim sAccessTableName As String

    Dim iAccessRecordCounter As Integer

     

    'SQLCE 3.5 Database

    Dim objSQLCEConnection As Data.SqlServerCe.SqlCeConnection

    Dim objSQLCECommand As Data.SqlServerCe.SqlCeCommand

    Dim sSQLCEConnection As String

    Dim sSQLCETableName As String

    'Set Table Name

    sAccessTableName = "XYZ"

    'Build SQL and Connection strings

    sAccessSQL = "Select * From " & sAccessTableName

    sAccessConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mstrAccessDataPath

    'Set Table Name

    sSQLCETableName = "XYZ"

    'Build SQL and Connection strings

    sSQLCEConnection = "Data Source=" & mstrSQLCEDataPath

    objSQLCEConnection = New Data.SqlServerCe.SqlCeConnection(sSQLCEConnection)

    Try

    'Access

    objAccessConnection = New Data.OleDb.OleDbConnection(sAccessConnection)

    objAccessDataAdapter = New Data.OleDb.OleDbDataAdapter

    objAccessDataAdapter.SelectCommand = New Data.OleDb.OleDbCommand(sAccessSQL, objAccessConnection)

    objAccessDataSet = New Data.DataSet

    objAccessCommandBuilder = New Data.OleDb.OleDbCommandBuilder(objAccessDataAdapter)

    ' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary

    ' key & unique key information to be retrieved unless AddWithKey is specified.

    objAccessDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    objAccessConnection.Open()

    objAccessDataAdapter.Fill(objAccessDataSet, sAccessTableName)

    'Create the AddCommand

    objSQLCECommand = New Data.SqlServerCe.SqlCeCommand("INSERT INTO " & sSQLCETableName & " (" & _

    "FieldID, " & _

    "FirstName, " & _

    "LastName, " & _

    "BirthDate) " & _

    "Values (?, ?, ?, ?)", objSQLCEConnection)

    objSQLCEConnection.Open()

    'Get values (Retrieve record(s) from DataSet)

    With objAccessDataSet.Tables(0)

    'Loop through the records

    For iAccessRecordCounter = 0 To .Rows.Count - 1

    'lstSearch.Items.Add(New ValueDescription(.Rows(iRecordCounter).Item("TeamId").ToString, sListString))

    objSQLCECommand.Parameters.Add(New Data.SqlServerCe.SqlCeParameter)

    objSQLCECommand.Parameters(0).Value = .Rows(iAccessRecordCounter).Item("FieldID").ToString

    objSQLCECommand.Parameters.Add(New Data.SqlServerCe.SqlCeParameter)

    objSQLCECommand.Parameters(1).Value = .Rows(iAccessRecordCounter).Item("FirstName").ToString & ""

    objSQLCECommand.Parameters.Add(New Data.SqlServerCe.SqlCeParameter)

    objSQLCECommand.Parameters(2).Value = .Rows(iAccessRecordCounter).Item("LastName").ToString & ""

    objSQLCECommand.Parameters.Add(New Data.SqlServerCe.SqlCeParameter)

    objSQLCECommand.Parameters(3).Value = .Rows(iAccessRecordCounter).Item("BirthDate").ToString

    objSQLCECommand.ExecuteNonQuery()

    Next

    objSQLCEConnection.Close()

    objSQLCECommand.Dispose()

    End With

    'Release Connection

    objAccessConnection.Close()

    ExportPlayers = True

    Catch objException As Exception

    ExportPlayers = False

    MessageBox.Show(objException.ToString, "Error")

    End Try

    Tuesday, April 29, 2008 12:38 PM
  • You can use the methods mentioned by Joao here: http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11213&atb.perform(details)=& and here: http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11003&atb.perform(details)=& - also on the desktop, as SqlCeResultSet is also available for desktop use.

    Tuesday, April 29, 2008 1:29 PM
    Moderator
  • I moved my Parameter.Add statements outside my loop and it significantly improved performance.  Thanks for the links!

    Tuesday, April 29, 2008 2:30 PM
  • If you are just doing a straight insert you can just use the Table Direct command type which will massively improve your performance.

     

    There is an article here which shows you how and gives you performance statistics.

    http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11003&atb.perform(details)

     

    Bro Num

     

    Just realised that ErikEJ already posted the above.

     

    It sounds like you have ignored the Table Direct approach. If you use the Table Direct command type you significantly improve your speed and simplify your code. Below is the code you would need to replace your code within the try catch (not tested).

     

    Code Snippet

    'Access

    objAccessConnection = New Data.OleDb.OleDbConnection(sAccessConnection)

    objAccessDataAdapter = New Data.OleDb.OleDbDataAdapter

    objAccessDataAdapter.SelectCommand = New Data.OleDb.OleDbCommand(sAccessSQL, objAccessConnection)

    objAccessDataSet = New Data.DataSet

    objAccessCommandBuilder = New Data.OleDb.OleDbCommandBuilder(objAccessDataAdapter)

    ' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary

    ' key & unique key information to be retrieved unless AddWithKey is specified.

    objAccessDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    objAccessConnection.Open()

    objAccessDataAdapter.Fill(objAccessDataSet, sAccessTableName)

     

    'Create the AddCommand

    objSQLCEConnection.Open()

    objSQLCECommand = objSQLCEConnection.CreateCommand()

    objSQLCECommand.CommandType = CommandType.TableDirect

     

    SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)
    SqlCeUpdateableRecord rec = rs.CreateRecord()

     

    'Get values (Retrieve record(s) from DataSet)

    With objAccessDataSet.Tables(0)

    'Loop through the records

    For iAccessRecordCounter = 0 To .Rows.Count - 1

    'lstSearch.Items.Add(New ValueDescription(.Rows(iRecordCounter).Item("TeamId").ToString, sListString))

    rec.SetString(0, .Rows(iAccessRecordCounter).Item("FieldID").ToString);

    rec.SetString(0, .Rows(iAccessRecordCounter).Item("FirstName").ToString);

    rec.SetString(0, .Rows(iAccessRecordCounter).Item("LastName").ToString);

    rec.SetString(0, .Rows(iAccessRecordCounter).Item("BirthDate").ToString);


    rs.Insert(rec);


    Next

    objSQLCECommand.Dispose()

    objSQLCEConnection.Close()

    End With

    'Release Connection

    objAccessConnection.Close()

    ExportPlayers = True

     

     

    Wednesday, April 30, 2008 11:23 AM