SQL insert with multiple records

Question

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.

Answer 1

You are on the right track. See the below link:

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

BTW, you may want to consider using SQLCommand parameters.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

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.

Answer 3

The loop wouldn't help much because every string actually won't be the same in my situation. Thanks for your advice.

---

-Nothing to see. Move along.

Answer 4

If you're using MS SQL Server 2008 or later, you can use table-valued parameters. There's a good explanation at www.sommarskog.se/arrays-in-sql-2008.html.

HTH,

Andrew