...

View Full Version : Insert Syntax- multiple rows



Eskimo
01-20-2003, 09: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)

BigDaddy
01-20-2003, 10: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.

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

whammy
01-20-2003, 11:33 PM
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).

Eskimo
01-21-2003, 12: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.

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

Example:



<%
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
oRs.AddNew()
' 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"
Next
'Push all new rows to the to the DB
oRs.UpdateBatch
oRs.Close
%>


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
oRs.MoveFirst
Do While Not oRs.EOF
Response.Write("Conflict: Name = " & oRs("Field0") & ", " & _
oRs("Field1") & ", " & _
oRs("Field2") & ", " & _
oRs("Field3") & ", " & _
oRs("Field4") & "<br>")
oRs.MoveNext
Loop


Hope this helps :cool:

Eskimo
01-21-2003, 02: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.

RadarBob
02-14-2003, 05: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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum