Solved

Need SQL query help

Posted on 2011-02-24
16
319 Views
Last Modified: 2012-06-22
SO I have 2 different databases that have same tables and fields. I need to compare one table and run query to find all rows that are in one database but not in the other. First I want to run a query that just shows me a report. Once I view the report and confirm what is missing I then want to run a query that will actually update the 2nd database with the rows that are missing. I am running SQL Server 2000
0
Comment
Question by:WiddleAvi
  • 6
  • 4
  • 4
  • +2
16 Comments
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 167 total points
ID: 34969908
Hi,

It would be better if you could provide the structure and primary key fields. Suppose you have below situation:
Database1 --> Table1 (Primary Key on myKeyId)
Database2 --> Table1 (Primary Key on myKeyId)

If you run the query:
SELECT * FROM DATABASE1.dbo.Table1 WHERE myKeyId NOT IN
(SELECT myKeyId FROM DATABASE2.dbo.Table1)

This will provide you the records which don't exists in Database2.

If you run the query:
INSERT INTO DATABASE2.dbo.Table1
SELECT * FROM DATABASE1.dbo.Table1 WHERE myKeyId NOT IN
(SELECT myKeyId FROM DATABASE2.dbo.Table1)

This will insert the required data in Database2.


rnm
0
 

Author Comment

by:WiddleAvi
ID: 34969999
I am not that familiar with SQL. Is it possible that there is no primary key ? I don't think there is. But there is like 3 or 4 fields that can be used in combination to see if a corrosponding records is in the 2nd database
0
 
LVL 7

Assisted Solution

by:kemi67
kemi67 earned 83 total points
ID: 34970049
Suppose that the databases are named MasterDB and SlaveDB and the table is named TblA
Suppose that the field TblA.ID is the field use to compare the two tables
Since you want to check which rows to insert, you need a temporary table having the fields that you want to see and, at least , the ID filed and a check field (for example a char(1) where you insert an X
The operation can be done in 3 steps
1) join the tables and fill the temporary table
2) check/uncheck rows on temporary table
3) insert row from temporary talbe
Let's see

1)
Suppose that the temporary table is on Master db and is named TmpA and had fields ID and Check and suppose we check all the differences

Use MasterDB
Truncate Table MasterDB.dbo.TmpA
Insert into MasterDB.dbo.TmpA (Check,ID) select 'X' as Check,  MasterDB.dbo.tblA.ID from MasterDB.dbo.tblA left join slaveDB.dbo.tblA on MasterDB.dbo.tblA.ID=SlaveDB.dbo.tblA.ID where SlaveDB.dbo.tblA.ID is NULL

2)
Edit the tmpA data unchecking rows
Select Check,ID from MasterDB.dbo.TmpA

3)
Insert selected data
Insert into SlaveDB.dbo.tlbA Select * from MasterDB.dbo.tblA inner join MasterDB.dbo.TmpA on MasterDB.dbo.tbla.ID=MasterDB.dbo.TmpA.ID

0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 34970066
Hi,
In that case for selection you can write:
SELECT a.*
FROM Database1.dbo.Table1 a LEFT OUTER JOIN Database2.dbo.Table1 b
ON a.myKey1 = b.myKey1 and a.myKey2 = b.myKey2 and a.myKey3 = b.myKey3
WHERE b.myKey1 IS NULL

And for insert:
INSERT INTO Database2.dbo.Table1
SELECT a.*
FROM Database1.dbo.Table1 a LEFT OUTER JOIN Database2.dbo.Table1 b
ON a.myKey1 = b.myKey1 and a.myKey2 = b.myKey2 and a.myKey3 = b.myKey3
WHERE b.myKey1 IS NULL



0
 
LVL 7

Expert Comment

by:kemi67
ID: 34970098
oh, i've posted after your second question.
So, supposing you use more than one field to compare, here is the modified version
 
1)
Use MasterDB
Truncate Table MasterDB.dbo.TmpA
Insert into MasterDB.dbo.TmpA (Check,ID1,ID2,ID3) select 'X' as Check,  MasterDB.dbo.tblA.ID1,MasterDB.dbo.tblA.ID2,MasterDB.dbo.tblA.ID3 from MasterDB.dbo.tblA left join slaveDB.dbo.tblA on MasterDB.dbo.tblA.ID1=SlaveDB.dbo.tblA.ID1 AND  MasterDB.dbo.tblA.ID2=SlaveDB.dbo.tblA.ID2  AND MasterDB.dbo.tblA.ID3=SlaveDB.dbo.tblA.ID3 where SlaveDB.dbo.tblA.ID1 is NULL

2)
Select Check,ID1,ID2,ID3 from MasterDB.dbo.TmpA

3)

Insert into SlaveDB.dbo.tlbA Select * from MasterDB.dbo.tblA inner join MasterDB.dbo.TmpA on MasterDB.dbo.tbla.ID1=MasterDB.dbo.TmpA.ID1 AND MasterDB.dbo.tbla.ID2=MasterDB.dbo.TmpA.ID2 AND MasterDB.dbo.tbla.ID3=MasterDB.dbo.TmpA.ID3

Open in new window

0
 

Author Comment

by:WiddleAvi
ID: 34970355
rajeevnandanmishra: - I will try this soon. I might post back with DB name and table and field names so I don't screw this up :)

kemi67: - I don't want to start adding any tables to DB even temp tables.
0
 

Author Comment

by:WiddleAvi
ID: 34970546
Ok so I don't screw this up. The table name is Rptrp and the fields I want to compare are Rptrp

SerialNo
StoreID
Usercode
Comcheck
UPC
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34970885
Well. If you don't want use a temporary table, you will only have the option to insert all the missing rows.
You can see what is missing but you can't decide which rows to insert and which not.
In this case rajeevnandanmishra (his second post) and my solution are the same and you will have
Note that rajeevnandanmishra second post and mine are more efficient than rajeevnandanmishra first post

For Selection:
SELECT a.*
FROM Database1.dbo.Table1 a LEFT JOIN Database2.dbo.Table1 b
ON a.SerialNo = b.SerialNo and a.StoreID= b.StoreID and a.Usercode = b.Usercode and a.Comcheck= b.Comcheck and a.UPC = b.UPC
WHERE   b.SerialNo IS NULL and  b.StoreID  IS NULL and  b.Usercode  IS NULL  and b.Comcheck  IS NULL and b.UPC  IS NULL

And for insert:
INSERT INTO Database2.dbo.Table1
SELECT a.*
FROM Database1.dbo.Table1 a LEFT JOIN Database2.dbo.Table1 b
ON a.SerialNo = b.SerialNo and a.StoreID= b.StoreID and a.Usercode = b.Usercode and a.Comcheck= b.Comcheck and a.UPC = b.UPC
WHERE   b.SerialNo IS NULL and  b.StoreID  IS NULL and  b.Usercode  IS NULL  and b.Comcheck  IS NULL and b.UPC  IS NULL
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 40

Expert Comment

by:Sharath
ID: 34972629
You can try EXCEPT. If both the tables are identical in structre, use * otherwise mention the column names.
select * from database1.table1 except select * from database2.table2 -- gives you all the recrods exist in first table which are not in second table.

Open in new window

check this on how to compare two tables using UNION.
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 34972874
Hi,

I think you get a fare glance now how to move on with your requirement.

In Kemi67 last comments, please change the "AND" to "OR" in the WHERE clause. I don't know, but think that will be benefecial.

You can also use Sharath comments. But you need to be on SQL 2005 or above version to run the "Except" operator.

0
 
LVL 7

Expert Comment

by:kemi67
ID: 34979435
No, don't change AND with OR, otherwise you will get wrong results. The AND clause is the only one that  finds an exact match.
If you use OR clause, you will get wrong results if some row have some NULL fields (for example null Comcheck or null UserCode)
0
 

Author Comment

by:WiddleAvi
ID: 34993595
So I actually found a saved query from our previous SQL admin :
select s.* from [192.168.35.100].vrp.dbo.rptrp s, rptrp h
where s.purchasedate>'12/1/2010' and s.serialno=h.serialno and h.storeid=s.storeid and h.comcheck=s.comcheck
and (s.upc<>h.upc or h.quantity<>s.quantity)

Now this returns about 150 rows. Now the problem I ran into is that I guess there is a primary key but I am pretty sure it allows dups. When I add the insert into line and run it I get this error:

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'RPTrp' with unique index 'PrimaryKey'.
The statement has been terminated.

What I would like to do it just change the serial number from those 150 results so they will be unique. They are right now a 6 digit number. I figure if I just add 2million to the current number they will be unique enough that we should be fine. So how I can I run a query to  update there 150 records to just add 2 million to the current serial no and then I can run the insert command
0
 
LVL 11

Expert Comment

by:mattibutt
ID: 34993601
i would say just use a dbcompare tool to accomplish this i have in the past used it for the mssql server i am sure there will be a tool available for mysql server
0
 

Author Comment

by:WiddleAvi
ID: 34993648
Would this work ? I think I am on the right track just not sure on the first part

update s.rptrp set SerialNo=SerialNo+2000000 from [192.168.35.100].vrp.dbo.rptrp s, rptrp h
where s.purchasedate>'12/1/2010' and s.serialno=h.serialno and h.storeid=s.storeid and h.comcheck=s.comcheck
and (s.upc<>h.upc or h.quantity<>s.quantity)
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 167 total points
ID: 34995077
Hi WiddleAvi,

You have gone quite well on this. Just a small change and it will work perfectly:
update s
SerialNo = s.SerialNo+2000000
from [192.168.35.100].vrp.dbo.rptrp s, rptrp h
where s.purchasedate>'12/1/2010' and s.serialno=h.serialno and h.storeid=s.storeid and h.comcheck=s.comcheck
and (s.upc<>h.upc or h.quantity<>s.quantity)

But This will update the base data. So, you would not be able to pick it up again (as serialno is also required in the joining). I don't think that will solve the total problem. Please insert the data in temp table first. Then update it. then insert it in the required table.

0
 

Author Closing Comment

by:WiddleAvi
ID: 35038668
I appreciate all your help. Here is what I ended up doing that worked

create view delme123 as
select s.* from [192.168.35.100].vrp.dbo.rptrp s, rptrp h  where s.purchasedate>'12/1/2010' and s.serialno=h.serialno and  h.storeid=s.storeid and h.comcheck=s.comcheck  and (s.upc<>h.upc or h.quantity<>s.quantity)

----> Separately run this ... <----

update delme123 set serialno=serialno+2000000

----> Then run this ... <----

Drop view delme123
Once I was able to update the Serialno to it was not conflicting I was able to run the insert into command
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now