Two DBs Two Tables Select Differences?

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

pointemanAsked:
Who is Participating?
 
CboudrozConnect With a Mentor Commented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
edemcsCommented:
Have you tried using SoundEx for the comparison instead?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
CboudrozCommented:
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
 
pointemanAuthor Commented:
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
 
pointemanAuthor Commented:
That did it, Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.