asked on

2 or more tables

comparing tables data between 2 databasesBookmark:Question: I have 2 databases which are identical( having 2 tables called tblhr(pc) and tblpm with pn and pc coulmns)

database1 have table called tblhr and tblpm having pc and pc and pn columns

database2 have table called tblhr having pc column and other table called tblpm have pc and pn columns

My task is to check database1 table columns i.e,pn (after joining the tblhr and tblpm) are matching with database2 tables

IF pn is present in database2 and also present in databse1 then i have to update the details in temp table i.e pn of database2 and pc of database1

IF pn does not exist in database1 after comparing with database2 then need to insert pn values with incremental value i.e max of pc +1

How can i implement this on a stored procedure

comparing tables data between 2 databasesBookmark:Question: I have 2 databases which are identical( having 2 tables called tblhr(pc) and tblpm with  pn and pc coulmns)
 
database1 have table called tblhr and tblpm  having pc and pc and pn columns
 
database2 have table called tblhr having pc column and other table called tblpm have pc and pn columns
 
My task is to check database1 table columns i.e,pn (after joining the tblhr and tblpm) are matching with database2 tables
 
IF pn is present in database2 and also present in databse1 then i have to update the details in temp table i.e pn of database2 and pc of database1
 
IF pn does not exist in  database1 after comparing with database2 then need to insert pn values with incremental value i.e max of pc +1
 
How can i implement this on a stored procedure

Open in new window

Kevin Cross

By update temp table, if you mean insert these values then do this:

INSERT INTO #temp (pn, pc)
SELECT a.pn, a.pc
FROM database1.dbo.tblpm a
INNER JOIN database2.dbo.tblpm b ON a.pn = b.pn

If you actually have a temp table with pn already listed and need to update then do this:

UPDATE t
SET t.pc = a.pc
FROM database1.dbo.tblpm a
INNER JOIN database2.dbo.tblpm b ON a.pn = b.pn
INNER JOIN #temp t ON t.pn = a.pn

For the second portion, try this:

INSERT INTO database1.dbo.tblpm (pn, pc)
SELECT a.pn
, ISNULL((SELECT MAX(pc)+1 FROM database1.dbo.tblpm), 1)
FROM database2.dbo.tblpm a
WHERE NOT EXISTS (SELECT NULL FROM database1.dbo.tblpm b WHERE a.pn = b.pn)

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

muneerz

ASKER

Thank You :)

Mark Wills

Small feeling of deja vu - a pleasure to help, and trust it is all working well for you, and most importantly thanks for saying "thanks"