Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Stillwater Ok
    Posts
    226
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Syntax- multiple rows

    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)
    Last edited by Eskimo; 01-21-2003 at 12:09 AM.
    "Never offend people with style when you can offend them with substance."
    --Sam Brown

  • #2
    Regular Coder
    Join Date
    Sep 2002
    Location
    Bugaha, NE
    Posts
    330
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Stillwater Ok
    Posts
    226
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much.
    "Never offend people with style when you can offend them with substance."
    --Sam Brown

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Stillwater Ok
    Posts
    226
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    "Never offend people with style when you can offend them with substance."
    --Sam Brown

  • #6
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually you can use ADO's Recordset.AddNew() functions to batch update the records.

    Example:

    Code:
    <%
    	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.


    Code:
    	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
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Location
    Stillwater Ok
    Posts
    226
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well you truely are a codeMonkey(). Thanks for the info.
    I may be able to use some of this in other areas later on.
    "Never offend people with style when you can offend them with substance."
    --Sam Brown

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    Round Rock, Texas
    Posts
    443
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variation on a theme - using a Stored Procedure

    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •