Solved: Multiple Inserts

[Imdabaum]

Okay this is random. I was asked to build a test database using existing data from an Oracle Database. I can't link the table because we don't want to modify the data in Oracle. I'm using TOAD which can create an insert statement for each row of data that you select.

The process involves:
1. Copy and Paste the code from TOAD to Access query
2. Cut everything but the first sql statement.
3. Execute sql
4. Paste over the executed statement with last copy
5. Repeat steps 2-4


Is there an easier way to do this?

[Imdabaum]

Nevermind... I found another option that allows you to save it as an xsl or xsd file. The xsd doesn't work too well but going from xsl works great... Love microsoft compatibility.

Although if anyone knows a way to do multiple inserts in directly in access without copying tables to and fro I would be interested to know.

[XLGibbs]

I am not sure what you mean by "multiple inserts"

A sequence of append queries, or a properly constructed insert statement would likely do.

[Imdabaum]

I am not sure what you mean by "multiple inserts"

A sequence of append queries, or a properly constructed insert statement would likely do.


Well one of the forms that Toad copies the data in a table is by creating an insert statement for each record. Hence you have multiple insert statements that look like this.



Insert into Table
(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9)
Values (entry1, entry2, entry3, entry4, entry5, entry6, entry7, entry8, entry9);
Insert into Table
(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9)
Values (entry1, entry2, entry3, entry4, entry5, entry6, entry7, entry8, entry9);
Insert into Table
(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9)
Values (entry1, entry2, entry3, entry4, entry5, entry6, entry7, entry8, entry9);
Insert into Table
(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9)
Values (entry1, entry2, entry3, entry4, entry5, entry6, entry7, entry8, entry9);



.... until every record is inserted. So my question was is there a way to run each of those at the same time without cutting all statements but the first line and repeating this cut and paste process after you run each individual statement.

[cmmsi]

If there is a need to restrict the update capability to Oracle, beginning with Oracle 9, you can create a READ ONLY ODBC connection. If your network restricts connection creation to the administrator, then you should be ok. Feel free to try it; even the schema owner, once connected through a read only odbc driver, will find their access to be read only. Give it a try.

[Imdabaum]

Thanks. I will try that and let you know how it works.