Link to home
Start Free TrialLog in
Avatar of intellie_ex
intellie_exFlag for Canada

asked on

Sql Query to compare and add

I'm looking for a query that will solve this as quick as possible.
I have 2 DB.  DB1 and DB2. This is a retail store.
DB1 has customers that have points on their account.  Tables AccountNumber and Points
DB2 has customers that have points on their account.  Tables AccountNumber and Points

There are the same customers in DB1 and DB2 but there are also different customers.
I need a query that will do the following:

1. Match customers from both DBs and add up their points.  
2. Any customer that was not matched add as new customer
3. The output has to go into DB2
 


Avatar of arnold
arnold
Flag of United States of America image

Here is something that might guide you in the right direction.
http://forums.devshed.com/ms-sql-development-95/nested-sub-query-exists-in-ms-sql-127661.html

Could you list what columns the accountnumber and points tables have

This might be what you are looking for.
update db2 set db2.points=db2.points+db1.points where db2.accountnumber=db1.accountnumber
insert into db2 (select  * from db1 where accountnumber not in (select accountnumber from db2))

Avatar of intellie_ex

ASKER

HI Arnold,

The AccountNumber and Points are columns

In the DB1 the table is called Customers and tables are called "AccountNumber" which stores the card # and "Points" which stores the total points earned to day
in DB2 the table is called RPT_TAB and tables are called "F1148" which stores the card # and "F64" which stores the total points earned to day.
You should try this on copies of the two databases name test1 and test2 (i.e. restore a backup version of db1 and db2 as test1 and test2.

ref: https://www.experts-exchange.com/questions/23065583/cross-database-view-or-something-in-SQL-Serve.html

You have two Tables on each Database:
DB1 has two tables Customers and Accountnumber
DB2 has two tables RPT_TAB and F1148

It is difficult for me at least to understand your DB/table/column info.

Similar to the referenced link above, provide the create statements for the four tables.

Which table for each database has the Accountnumber and points column?

What are card #?  How do they relate to the Accountnumber column and accountnumber table in DB1?

Similarly same columns relationship with F1148 and F64 tables in db2.

From db1 to db2 you are moving from two tables to three tables.

hi, try this

To update existing records
UPDATE DB2 SET DB2.Points=DB1.Points+DB2.Points
FROM DB2 INNER JOIN DB1 ON (DB1.AccountNumber=DB2.AccountNumber)


To add new records
INSERT INTO DB2 (AccountNumber,Points)
SELECT DB1.AccountNumber, DB1.Points
FROM DB1 LEFT JOIN DB2 ON (DB1.AccountNumber=DB2.AccountNumber)
WHERE DB2.AccountNumber IS NULL
I'm not explaing correct.  will try again with some changes

I have 2 tables under the same DB

table 1 has 2 columns
column 1 = AccountNumber - This hold a card number which every customer has
column 2 = Points - this hold total points a customer earned on this card

table 2 has 2 columns
column 1 = F1148 - This hold a card number which every customer has
column 2 = F64 - this hold total points a customer earned on this card

What I need is:

1. find matching Card number between 2 tables and calculate the different in the points and add that difference to the smaller number of the 2.
3.  Any card numbers that did not match I need it to add as new.

I have 3 table that I want to dum this it's called customersfinal
>find matching Card number between 2 tables and calculate the different in the points and add that difference to the >smaller number of the 2.

Getting the difference between 2 numbers add adding it to the smaller of the 2 will result to the larger number.
e.g
10 and 4 -> points 1 and 2
10 - 4= 6 -> difference of 2 points
6 + 4 = 10 -> sum of difference and smaller number

>I have 3 table that I want to dum this it's called customersfinal
Does customersfinal table have any values in it? If it does, will the existing records be replaced by the new value?
The final tables does not have any records, I want the results to be inserted in to it
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial