CODEWITHSUNDEEP
c#vb.netsqlite
Sap
Sap

Reputation: 179

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

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?

Upvotes: 0

Views: 2494

Answers (6)

btlog
btlog

Reputation: 4780

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.

Upvotes: 1

ajay_whiz
ajay_whiz

Reputation: 17931

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

Upvotes: 0

Dan Puzey
Dan Puzey

Reputation: 34198

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

Upvotes: 2

Coding Flow
Coding Flow

Reputation: 21881

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.

Upvotes: 2

thelost
thelost

Reputation: 6694

Is cmd.ExecuteNonQuery() executed within each iteration ?

Upvotes: 0

Henri
Henri

Reputation: 5113

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?

Upvotes: 0

Related Questions