Which method is most effecient

[ccampbell]

Which of the following methods is the most efficient. I have a program written that inserts values into a database. I have about 200 values that I have to insert into a table in a database
1. Load each value into a variable and insert them all at one time with one SQL call
2. Insert each one individually making about 200 individual SQL calls. Which one would work best? THanks in advance for any advice

 

[cfk]

Making a single call to the database is quicker
9 times out of 10.
If your using MSSQL2000 you can concatenate all
you queries into a single call.

SQL = "
INSERT INTO myTable(a) VALUES('b0')
INSERT INTO myTable(a) VALUES('b1')
...
INSERT INTO myTable(a) VALUES('bn')
"

IN MSSQL2000 treat the string you send as a stored procedure
although if it gets too long think about using an
actual stored proceedure