Prev: VB Control Positioning in MS Word
Next: Mass updating a database with unique key
From: Faraz Azhar on 31 Jan 2010 15:08
Hello.

I have a database with quite some data in it. The DB is MS Access db.
it has a primary key which is alphanumeric key.

What im trying to accomplish is that I have a lot of data that I want
to add to that database. Now most of my data my already exist in the
database. Obviously if i add the data which has the same key as the
data which already exists, it will give error that duplicate values
are being created.

I need suggestions on how to do this the fastest way: Add 2000 or 3000
records to a ADO database, furthermore add records only if they dont
previously exist in the database.

For this i have two techniques:

1) First i may search the entire database for that unique key. If key
exists then I wont add new record. If key doesnt exist then ill
continue and add the record.

2) or i would just keep on adding records. ADO will give error that
duplicate values are being created. If error arises (ie. record
already exists in the database) then ill skip that newly added item.
If error doesnt arise that means record was unique in the database.

Which of the above two ways would you guys recommend ? Is there any
other way round which is faster than this?
From: Tony Toews [MVP] on 31 Jan 2010 17:12
Faraz Azhar <itzfaraz(a)gmail.com> wrote:

>1) First i may search the entire database for that unique key. If key
>exists then I wont add new record. If key doesnt exist then ill
>continue and add the record.

What do you mean by "search the entire database for that unique key"
If that field(s) is indexed then you only need to ask for that record.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
From: Faraz Azhar on 31 Jan 2010 23:15
@Tony: I meant like running a query (SELECT * FROM abc WHERE
MyKey=123). If the recordset has any records then it means this item
already exists so I dont have to add it again.

@Ralph: Its going to be a routine activity. My app will be
synchronizing some large data with the data that is already in the
database. So I need to add only those items which don't exist in the
database.

Thanks everyone. I thought a query of some kind could be developed to
add mass data into a database without producing duplicates. Anyway
I'll try option #2... its more convenient.
From: Larry Serflaten on 1 Feb 2010 08:12

"Faraz Azhar" <itzfaraz(a)gmail.com> wrote
> Thanks everyone. I thought a query of some kind could be developed to
> add mass data into a database without producing duplicates. Anyway
> I'll try option #2... its more convenient.

I was thinking that should be possible too. But, I don't do work with
DBs or SQL, so I thought I'd let the more experienced tackle your
question.

Some thoughts on it were as follows:

Assuming the new data was already in a table, you would use the
SELECT INTO type query to add new records to the main table.

To get the right group, you need a list of keys that were not already
in the main table to use the WHERE ... IN ... type of clause.
WHERE ... NOT IN ... is also available if it is easier to list the
duplicated keys from the new data table.

So, thus far you have a query in the form of:

SELECT INTO MainTable
FROM NewTable
WHERE NewTable.Key [NOT] IN <key list>

What's left is to develop a query that either lists keys from
the new table that are unique to the main table, (WHERE ... IN ...)
or, list all the keys (or at least the duplicated keys) from the
main table (WHERE ... NOT IN ...)

The second list seems easier to get (for me) but the first list could
be shorter, and probably faster. As Ralph points out, timed trials
could help you make informed decisions....

So, using the second list (NOT IN clause) a single query might
look like:

SELECT INTO MainTable
FROM NewTable
WHERE NewTable.Key NOT IN (SELECT Key FROM MainTable)

Again, I don't use SQL very often, and have no easy way to test
what would or would not work. I just wanted to suggest you
might be able to create a stored procedure that would cover the
task, given the new data was already in a temporary table.

Good luck!
LFS


From: Larry Serflaten on 1 Feb 2010 08:53

"Larry Serflaten" <serflaten(a)usinternet.com> wrote

> Assuming the new data was already in a table, you would use the
> SELECT INTO type query to add new records to the main table.

Doh! Every occurance of SELECT INTO (a make table query)
should have been INSERT INTO (an append query)

Once again, showing my ignorance of DB programming!

Oh well....
LFS


 |  Next  |  Last
Pages: 1 2
Prev: VB Control Positioning in MS Word
Next: Mass updating a database with unique key