How to do bulk insert for over 1000 records using sqlite?

Question

I am trying to insert 11000 records using sqlite in database but values arent inserted though program executes without error.

Dim s As New System.Text.StringBuilder("")

sql = "insert into mulRecords1 (symbol, date_p,open_p,high_p,low_p,close_p,vol_p,oi_p) values ('@symbol', '@date_p','@open_p', '@high_p', '@low_p', '@close_p', '@vol_p', '@oi_p')"

cmd = New SQLiteCommand(sql, cnLite)
cmd.CommandText = sql

For i As Integer = 1 To 11000

cmd.Parameters.AddWithValue("@symbol", "test")
cmd.Parameters.AddWithValue("@date_p", "2010-08-20 12:00:00")
cmd.Parameters.AddWithValue("@open_p", 10)
cmd.Parameters.AddWithValue("@high_p", 10)
cmd.Parameters.AddWithValue("@low_p", 10)
cmd.Parameters.AddWithValue("@close_p", 10)
cmd.Parameters.AddWithValue("@vol_p", 10)
cmd.Parameters.AddWithValue("@oi_p", 10)

Next

cmd.ExecuteNonQuery()

How should i insert?

Answer 1

My guess is that the cmd.ExecuteNonQuery() should be before the next. Probably only the last record is inserted now.

Moreover, you're inserting all the same rows i dont see the point of having 11K times the same row, this is probably a mistake?

Answer 2

Is cmd.ExecuteNonQuery() executed within each iteration ?

Answer 3

Your loop just adds the parameters over and over again then does a single insert you need to execute the query every loop iteration e.g.

Dim s As New System.Text.StringBuilder("")

sql = "insert into mulRecords1 (symbol, date_p,open_p,high_p,low_p,close_p,vol_p,oi_p) values ('@symbol', '@date_p','@open_p', '@high_p', '@low_p', '@close_p', '@vol_p', '@oi_p')"



For i As Integer = 1 To 11000

cmd = New SQLiteCommand(sql, cnLite)
cmd.CommandText = sql

cmd.Parameters.AddWithValue("@symbol", "test")
cmd.Parameters.AddWithValue("@date_p", "2010-08-20 12:00:00")
cmd.Parameters.AddWithValue("@open_p", 10)
cmd.Parameters.AddWithValue("@high_p", 10)
cmd.Parameters.AddWithValue("@low_p", 10)
cmd.Parameters.AddWithValue("@close_p", 10)
cmd.Parameters.AddWithValue("@vol_p", 10)
cmd.Parameters.AddWithValue("@oi_p", 10)

cmd.ExecuteNonQuery()

Next

This also assumes you do actually want to insert 11000 identical rows.

Answer 4

At the moment you just add the parameters 11000 times and then execute the command once.

I suspect that you need to put cmd.ExecuteNonQuery() inside your loop (before the Next).

Answer 5

consider changing your For block to

For i As Integer = 1 To 11000

cmd.Parameters.AddWithValue("@symbol", "test")
cmd.Parameters.AddWithValue("@date_p", "2010-08-20 12:00:00")
cmd.Parameters.AddWithValue("@open_p", 10)
cmd.Parameters.AddWithValue("@high_p", 10)
cmd.Parameters.AddWithValue("@low_p", 10)
cmd.Parameters.AddWithValue("@close_p", 10)
cmd.Parameters.AddWithValue("@vol_p", 10)
cmd.Parameters.AddWithValue("@oi_p", 10)

cmd.ExecuteNonQuery()

cmd.Parameters.Clear()

Next

Answer 6

There are a couple of issues.

You need to call the ExecuteNonQuery in the for loop. Make sure you clear the parameter list in each iteration if you are adding new parameter values for each iteration.

For i As Integer = 1 To 11000
    cmd.Parameters.Clear()

    // Add parameter values (I removed for clarity)

    cmd.ExecuteNonQuery()
Next

Remove the single quotes (') from around the parameters. Should be

sql = "insert into mulRecords1 (symbol, date_p,open_p,high_p,low_p,close_p,vol_p,oi_p) values (@symbol, @date_p,@open_p, @high_p, @low_p, @close_p, @vol_p, @oi_p)"

I apologise for the C# comment in my code, couldn't get syntax highlighting to work with vb comment.