Howto merge into a database

Question

I am having trouble figuring out the best way to approach this problem,

I have two different list of items, one in the DB, one not. Both list have item ids and quantity, I need to merge the two list into the DB.

My first inclination is to just pull all of the items out of the DB then merge the two list in the application logic. I would then delete the old list from the DB and insert the new merged list.

I figure I could also update/inserts for each of the items I want to merge into the DB, however this would mean many queries (maybe there is a stored procedure I could write, but i am not that hot with stored procedures).

I would like to know if SQL or application logic will perform better in this situation?

I would also like to hear how others would tackle this problem.

thanks.

UPDATE:

Here are some more details,

This is not a one time merge, it will happen several times. Basically I have an object in my application that holds a list of items, eventually these items need to be put into the DB. There is already items in the DB, so I can not just insert the items, I must increase the quantity of an item in the DB if two items have the same id and insert items with unique ids.

ONE MORE UPDATE:

using MySQL and PHP

Answer 1

More details would be helpful, including the database platform you're using.

In the meantime... this sounds like it could be something like a product catalog which you have in the database, and some similar dataset outside the database that you're trying to merge in, like maybe your Barnes & Noble store in Bumpster, Nebraska folded and you're reintegrating their inventory into your main store.

If the "product" IDs are the same in both systems, you could maybe create a loop structure in a stored proc which would iterate through the external collection, count the number of each, and then increment some total in your main product table.

Answer 2

Create two temp tables, one to be a copy of the old DB data and the other to contain what currently is not in the DB. Then do a grouping query against the bag union on the two tables.

For example, given the following initialization point, and assuming MySQL syntax:

create temporary table a (name varchar(255), quantity int);
create temporary table b (name varchar(255), quantity int);
insert into a values ('fork', 1), ('spoon', 2);
insert into b values ('fork', 2), ('knife', 1);

You could then simply run the following query to get the result you desire.

select name, sum(quantity) AS quantity 
  from ((select * from a) union all (select * from b)) c
  group by c.name;

Which would give you the result:

 +-------+----------+
 | name  | quantity |
 +-------+----------+
 | fork  |        3 | 
 | knife |        1 | 
 | spoon |        2 | 
 +-------+----------+

If you wanted to store the result back into the original data table, just delete from the original table after creating the two temp tables and then prepend the whole SELECT statement with INSERT INTO original_table SELECT ...

Answer 3

You didn't mention what programming language or database you are dealing with, but I would look into the availability of an "UPSERT" command for your chosen database.

For Oracle, this would be the MERGE INTO statement: http://www.psoug.org/reference/merge.html I think SQLServer has an "IF FOUND" statement or something like that (I dont use SQLServer often, sorry).

Basically an "upsert" is an "insert or update", so instead of having to decide whether or not the row with a given ID already exists, you can just say "here is my data, if this id doesn't exist, insert it, if it does already exist, then update it."

Answer 4

In mysql use

INSERT INTO TABLE1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c = c+3

you would have to change syntax if you use a different database.

If you use a temporary table approach there is no need to delete the main table, and that would likely be very bad.

CREATE TEMPORARY TABLE temp1 .....identical to main table
INSERT everything into temp1

INSERT (colnames ...) INTO main1 SELECT (colnames ..) FROM temp1 
    ON DUPLICATE KEY UPDATE onhand = main1.onhand + temp1.onhand