queryStr = "INSERT INTO tbl_user_ldr_responses (rec_num, ques_no, response, comment) " & _ recnum & ", " & "1, " & "'', " & reader.GetValue(1) & " UNION ALL" & _ recnum & ", " & "2, " & "'" & reader.GetValue(2) & "', " & "''"
How can I fix my string so that it will resemble the below SQL code to insert multiple records with one insert command?
INSERT INTO thetable (field1, field2) SELECT value1, value2 UNION ALL SELECT value3, value4 UNION ALL . . . SELECT value5, value6;
-Nothing to see. Move along.
You are on the right track. See the below link:
BTW, you may want to consider using SQLCommand parameters.
Paul ~~~~ Microsoft MVP (Visual Basic)
- Edited by Paul P Clement IVMVP Friday, May 11, 2012 10:19 PM
Adding to Paul's advice, if you stay with the approach that uses UNIONs you might want to limit the batched number of unioned statements to a few hundred because the compilation time may get to be very slow, thus defeating the purpose of why you are combining the inserts.
Also, you might want to consider using a StringBuilder (http://msdn.microsoft.com/en-us/library/system.text.stringbuilder.aspx) if your statement gets long because of the memory hit of so many concatenated strings. Besides, you would be better served to build the query statement in a loop because of all the repeated text parts.