Appending only Unique Data

[Jamie]

Hi all,

I use Cognos to pull raw data from Oracle. I take this data and put it into Access 2010 tables to then run queries. Because the people creating the data in Oracle are often slow, I run back data to catch missed information, typically two months. For October, for example, I'll run October and also September and August.

While I have new data, I also have a great deal of duplicate data. How can I ensure that I append only the new data and exclude the duplicate data?

Thanks!

 

[BlueIshDan]

Here is a good example:

INSERT INTO myTable(columns...)
Select values...
WHERE NOT EXISTS
   (SELECT *
    FROM myTable
    WHERE pk_part1 = value1,
        AND pk_part2 = value2)

Found here.

 

[plog]

Why not link directly to Oracle from Access and run your queries on live data? Create (or use your existing) ODBC connection: http://support.microsoft.com/kb/110093

 

[BlueIshDan]

My guess is the people managing the database wouldn't quite appreciate that. (Is their thought in mind) lol

 

[plog]

Why not? He already has access in some form, what's it matter if its in another?

 

[gemma-the-husky]

I prefer not to link to real data in discrete systems, unless I have a particular need. I might be sensible not to alter the backend data - but others may not be.

 

[jdraw]

Just a thought for consideration:

If the Oracle database is your production database, and Cognos is your reporting/querying tool, why do you have to go to Access to "catch" missed information.
I suggest you may have a problem related to processes with your production system, and that should be reviewed and corrected. If you can identify the reason for the lag, then you could do reports/queries that recognize the lag. But if you can't rely on your production system, you have a major issue (in my mind).

I have worked with online corporate data bases where we took snapshots (monthend, quarterend, yearend, fiscal yearend) to allow people to do various analyses and reports from a ReadOnly "point in time database" in nonOracle(Access/vba) and Oracle.