View Full Version : Insert Syntax- multiple rows

01-20-2003, 10:36 PM
I'm not sure if this is even in the right forum

I'm using SQL Server.

Does anyone remember the syntax for inserting multiple rows into a database via one SQL statement. I really think I used to know this, but for some reason my head is fried and I can't recall, the help files I have are worthless and I haven't been able to find the answer anywhere else online. Is it because I'm wrong, and its just impossible?

INSERT INTO tableName (field1, field2, field3,....fieldN) Values(value1a,value2a,value3a,....valueNa), Values(value1b,value2b,value3b,....valueNb)

01-20-2003, 11:11 PM
My SQL book says:

"INSERT" usually inserts only a single row. To insert multiple rows, you must execute multiple INSERT statements. The exception to this rule is INSERT SELECT, which can be used to insert multiple rows with a single statement--whatever the SELECT statement returns will be inserted by the INSERT."

No examples were given. I personally have not done it.

01-20-2003, 11:35 PM
Thank you very much.

01-21-2003, 12:33 AM
As far as the syntax for your above statement, it should be:

"INSERT INTO tablename () VALUES ()"

Not sure about inserting multiple rows, as I have never done it either (except for simple looping).

01-21-2003, 01:09 AM
Thanks for the correction, I guess I left it out when I was asking the question. I'll fix that so if anyone else is bumming through here doesn't assume thats right.

01-21-2003, 03:43 AM
Actually you can use ADO's Recordset.AddNew() functions to batch update the records.


Dim oConn,oRs,sSQL,i

set oRs = server.CreateObject("ADODB.RECORDSET")
oRs.CursorType = 2 ' Dynamic Curor
oRs.CursorLocation = 1 ' Server Side
oRs.LockType = 4 ' Batch Optimistic

set oConn = server.createobject("ADODB.CONNECTION")
oConn.ConnectionString="Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & Server.MapPath("myDatabase.mdb")
sSQL = = SELECT Field0, Field1, Field2, Field3, Field4, FROM myTable"
oRs.Open sSQL,oConn
For i = 0 To 4
' Add New Row
' Add new Column Data
oRs("Field0") = "my New Value 0"
oRs("Field1") = "my New Value 1"
oRs("Field2") = "my New Value 2"
oRs("Field3") = "my New Value 3"
oRs("Field4") = "my New Value 4"
'Push all new rows to the to the DB

Although this method is "quicker" than using transactions, it is dirtier in that even if one update fails, it will keep pushing data into the DB. Thus you can get corrupted data in the targeted table(s).
There is an "obscure" filter: adFilterConflictingRecords to return the failed updates. But once again this only lists the fails and does not rollback the bad updates.

oRs.Filter = adFilterConflictingRecords
Do While Not oRs.EOF
Response.Write("Conflict: Name = " & oRs("Field0") & ", " & _
oRs("Field1") & ", " & _
oRs("Field2") & ", " & _
oRs("Field3") & ", " & _
oRs("Field4") & "<br>")

Hope this helps :cool:

01-21-2003, 03:54 AM
Well you truely are a codeMonkey(). Thanks for the info.
I may be able to use some of this in other areas later on.

02-14-2003, 06:09 PM
First Some questions then some background
* Does batch update execute the stored procedure once or once for each record - as it would with non-batch updates.

* Does a recordset push its data as a *set* to a stored procedure?

*When using a recordset to retreive data from A DB, it's handled as a set. Is the data handled that way when inserting data?

What I'm Getting at
I have working code that adds new records to a table, or updates existing records. Now I want to implement deleting existing records.

I want to handle user data deletions on the database by first deleting all records (with the appropriate key) then INSERTING the complete set of data passed in. This method will significantly limit the code modifications. It will keep me from having to create a new, unnecessary "delete this record" field - (which is VERY problematic because we're talking about a multiple-select HTML field here); and saves modifying all my VBScript, ASP, HTML, and JavaScript code!!

The current server-side code, via a loop, is passing individual records one at a time via an ADO Command object. This causes the DELETE to execute for every record and only the very last record set ends up on the table.

I'm hoping that recordset batch updating would execute the stored procedure only once for the entire set of records. Then the delete would behave correctly.