Inserting a Group/Batch of Records?

[Running Bear]

I'm trying to Insert multiple records in one batch. Does anyone know how to do it?

I'm working in ASP and trying to insert into an Oracle DB
The code below is test. In reality I don't know how may records will be inserted so the sql is built up using a loop.

I've been trying to use one query see below but get an error returned 'query not ended properly'


<%
Dim cn, rs
Dim SQL

' Define the query
SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 1, 'This is ') "

SQL = SQL & "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 2, 'Some Text') "


' Define the Connection and Recordset Objects
Set cn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")

' Open the Oracle Connection using an ODBC DSN
cn.open "DSN=;uid= ;pwd="

' Open the Recordsets using the open connection
rs.Open SQL, cn
%>


Cheers Al

[rcreyes]

First of all you have to have a GO in between the INSERT, i.e.

Insert INTO Table(a, b, c) values (1,2,3,)
GO
Insert INTO Table(a, b, c) values (4,5,6)
GO


Another way of doing is to use the UNION statement, using the same example:

Insert INTO Table (a, b, c)

Select
1,2,3
UNION
Select
4,5,6


I hope this helps.....

Thanks,
Ray

[Running Bear]

Hi Ray,

Thanks for your ideas, I've just tried these two without any joy. Can you see where I've gone wrong?


' Define the query
SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 1, 'This is ') Union "

SQL = SQL & "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 2, 'Some Text') "

and

' Define the query
SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 1, 'This is ') Go "

SQL = SQL & "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(MyTestTable_ID.nextval, 2, 'Some Text') Go "

[rcreyes]

Sure -- first of all you cannot use VALUES when using the UNION statement, also it look like use are inserting the text MyTable_Id.nextval and NOT the actual value in the MyTestTable_ID column. I re-wrote your query below:

' Define the query
SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(" & MyTestTable_ID.nextval & ", 1, " & Chr(39) & "This is" & Chr(39) & ") Go "

SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Values(" & MyTestTable_ID.nextval & ", 2, " & Chr(39) & "Some Text" & Chr(39) & ") Go "

-- now using the UNION statement
SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Select " & MyTestTable_ID.nextval & ", 1, " & Chr(39) & "This is" & Chr(39) & " UNION "
SQL = SQL & "Select " & MyTestTable_ID.nextval & ", 2, " & Chr(39) & "Some Text" & Chr(39)

Also, I used MS/SQL and NOT ORACLE so I am not sure if the MyTestTable_ID.NextVal statement is permitted or not.

Let me know what happen .......

Thanks,
Ray

[Running Bear]

Ray,

Thanks again for your help with this I've just tried the Union statement you wrote, I had to put the primary key back into quotes because it's an oracle object when you run outside the quotes you get the error 'object not found'. I now get the following error.



SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Select MyTestTable_ID.nextval, 1, " & Chr(39) & "This is" & Chr(39) & " UNION "
SQL = SQL & "Select MyTestTable_ID.nextval, 2, " & Chr(39) & "Some Text" & Chr(39)


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00923: FROM keyword not found where expected

So I altered the query to this -

SQL = "Insert into MyTestTable ("
SQL = SQL & "MyTestTable_ID, ANumber, SomeText) "
SQL = SQL & "Select MyTestTable_ID.nextval, 1, " & Chr(39) & "This is" & Chr(39) & " FROM MyTestTable UNION "
SQL = SQL & "Select MyTestTable_ID.nextval, 2, " & Chr(39) & "Some Text" & Chr(39) & " FROM MyTestTable"

Which has resulted in this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-02287: sequence number not allowed here

[Roelf]

why not prepare the recordset and update it in one go.

prepare the recordset with the rs.addnew method, when it is finished, do a rs.updatebatch and the whole recordset is in the db (at least as far as i remember)

some documentation: http://www.devguru.com/Technologies/ado/quickref/recordset_updatebatch.html

[Running Bear]

Roelf,

I'll give that a go

[whammy]

Since you started another thread that seems to continue this one, I'm going to close this thread to avoid confusion. Good luck!