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

Answer 1

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.

 

Answer 2

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...

Answer 3

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).

 

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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