Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan