Checking Data in a Large table against a smaller table

Question

Hi,

I have 2 tables one with a lot of records(table 1), and a second(table 2) with similar data but with far fewer records.

On a regular basis i need to add a marker to records in the larger table where there is a corresponding record in the smaller table. For example this could be an email address.

So if email address exists in the smaller table(table 2) mark this in the larger table(table 1)

Now, i know this is a bit vague but the actual data isnt relevant nor is table/column names. What im looking for is ideas / suggestions as the most efficient way to do this.

Do i run a procedure that....

A) checks a record in one table and then looks for a corresponding record in the other? Then marks this record if it has a match. Which way around is best?

B) Do something with temporary tables?

c) something totaly different? Please give pointers.

I hope this makes sense, and i hope you can help.

Thanks!

Answer 1

Do an UPDATE that consists of an INNER JOIN between the two tables on email address.

Answer 2

Since you are on SQL 2005, you cannot use the MERGE command. You have to use an INSERT and an UPDATE statement to do this.

Once you write the SQL to do this, you can create a SQL Job that executes this statement periodically and schedule it.

Here is how to schedule a job: http://msdn.microsoft.com/en-us/library/ms190268.aspx

Answer 3

Roughly speaking, do something like this:

Update LargeTable
Set Marker = 1
From LargeTable l
inner join SmallTable s
on s.ID = l.ID
Where s.SomeField = 'Criteria'

Answer 4

Why not create a trigger that will automatically update the first table when a record is put into, delted from or changed in the second table. Or better yet,, why not just join to the second table when you want to know this information instead of denormailzing to store in the first?