multiple insert into mysql

Question

0.00/5 (No votes)

See more:

Hi,

I'm having 2 string array and 1 integer array of size 100. I need to insert them (100 rows) into table.

query= insert into storage (box_no,item_name,amount) values x,y,z

for(i=0;i<100;i++)

x=arr1[i]
y=arr2[i]
z=arr3[i]

How can I optimize the query, may be using command.parameter.addwithvalue

Thanks

Note: Not sure if the question is regarding MySQL (Tags are there), because original question was for msql (maybe a typo for mssql).

Posted 14-Sep-11 11:09am

DEB4u

Updated 14-Sep-11 11:57am

Rutvik Dave

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rutvik Dave · Accepted Answer · 2011-09-14T11:51:00

I am assuming that you are using MySQL, because in your original question you didnt mentioned MySQL. But I see tags added as MySQL and VB.NET

if there are only 100 rows you want to insert in a loop, you will not see much of a difference when you optimize. but if you want to insert lot of rows (more than 10,000) in bulk, the best way is to create a data adapter then fill a dataset, set the primary key on dataset same as actual table, then add rows into the dataset (in memory), and then fire datadapter.update() and pass the dataset. this way the data adapter will insert the records most effeciently (same when you want to update in bulk).

but in your case you should not worry too much about the performance as it's only 100 rows (you will not see that milisecond improvement). but you should avoid any sql injection attacks by passing the values with parameter. for security never use inline sql.

(like

SQL

string strQuery = "Select * From Users Where Password = '" + strPassword + "' And UserName = '" + strUserName + "'

").

always use parameter, (or call stored procedure)

VB

Dim da As MySqlDataAdapter = New MySqlDataAdapter()
Dim cmd As MySqlCommand
Dim parm As MySqlParameter
' Create the SelectCommand.
cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
da.SelectCommand = cmd
' Create the InsertCommand. put this in a loop
cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id, ?name)", conn)
cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )
da.InsertCommand = cmd

you can find more information about data adapter on this link[^].