?
Solved

Two DBs Two Tables Select Differences?

Posted on 2011-05-11
6
Medium Priority
?
339 Views
Last Modified: 2012-06-21
I need to find rows in DB1.dbo.NewSheet1 not found in DB2.dbo.Master1 using two columns FirstName and LastName. Sounds simple but having difficulty with MSSQL running this query very long, returning many duplicates. I used 'Select Distinct' to eliminate the dups, but need to select all and insert * into DB2.
Also tried "Select Distinct DB1.dbo.NewSheet1.* ", but no good return either. Help

Select * From DB1.dbo.NewSheet1
 Left Join DB2.dbo.Master1 On DB1.dbo.NewSheet1.FirstName != DB2.dbo.Master1.FirstName
 And DB1.dbo.NewSheet1.LastName != DB2.dbo.Master1.LastName

0
Comment
Question by:pointeman
6 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35739558
id you need to merge two tables then folow this :

http://technet.microsoft.com/en-us/library/bb510625.aspx

this will alow you to add missing rows from one table to the other and update(iif needed) the target table with new data.
0
 
LVL 8

Expert Comment

by:edemcs
ID: 35739821
Have you tried using SoundEx for the comparison instead?
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 35739842
When you do a join on DB1.dbo.NewSheet1.FirstName != DB2.dbo.Master1.FirstName

that's means that if fistname are different the row will be join.  So if firstname = 'pierre' it will be join whit all the row in second table that are différent from 'pierre', making many duplicate and it's really not doing what you want to do.
 
you need to make a normal "on" clause firstname = firstname and found the row not match.



Select 
	* 
From 
	DB1.dbo.NewSheet1 n
	Left Join DB2.dbo.Master1 m
		On n.FirstName = m.FirstName
		  And n.LastName = m.LastName
where 
	m.FirstName is null -- row existing in table NewSheet1 but  not match in table  DB2.dbo.Master1

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:pointeman
ID: 35740010
Okay, use = instead of != although I can't find another row to use the where clause...

The two tables don't have identical columns, only FirstName and LastName match. I'm tring to combine imported Excel spreadsheets. I already performed and update on Master1 with the other tables and all run well. Just need to eventually Insert the missing rows from the other tables into Master1, hopefully without dups.  
0
 
LVL 7

Accepted Solution

by:
Cboudroz earned 2000 total points
ID: 35740065
make sure you have no duplicate in newsheet table.

Select
      firstName
      , lastName
      , count(*)
From
      DB1.dbo.NewSheet1 n
group by
      firstName
      , lastName
having
      count(*)>1


if no duplicate you can do the insert like this:


insert into DB2.dbo.Master1
(...)
Select 
	... 
From 
	DB1.dbo.NewSheet1 n
where 
	not exists
	(
		SELECT 
			*
		FROM 
			DB2.dbo.Master1 m
		WHERE 
			n.FirstName = m.FirstName
			And n.LastName = m.LastName
	)
	-- and other condition
	AND status = 1
	and date > getdatE()

Open in new window

0
 

Author Closing Comment

by:pointeman
ID: 35740248
That did it, Thank You!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 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