How to do multiple rows insert?

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;

Already tryed 2 methods.

1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();

command.CommandText

= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";

if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();

command.ExecuteNonQuery();

Connection.Close();

Using this method i found out bug (or so i think).

I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

Hi,
why open, execute and close?

Why not open at the beginning, do all your executes, and only close when you exit the program?

That should be much quicker

Pete

|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.

But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.

If I could cut data incertion to ~2 mins than it would be OK.|||

Try using parameterized queries for bulk inserts.

// Arranging Data in an Array.

const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;

// Do the inserts using Parameterized queries.

Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";

for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}

Connection.Close();

|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()

For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand

Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"

Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()

Debug.Print(Date.Now)

For loopCount = 1 To 10000

rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)

Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try

Debug.Print(Date.Now)

End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.

My solution in the end.

connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines

strArray = buffer.Trim().Split(separator);

if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();

strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}

strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();

Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.

By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)

And in data file values are writen:

someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....

But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.

P.S.
Again thank you very much.


|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar