Compare 2 tables in sql 2005 and append if does not exist

Hello,
I need to compare 2 tables and if the record does not exist in one, then I need to append it to the second table and if it exists, I need to update the information.
I wrote a procedure but I am getting an
error:'Msg 4104, Level 16, State 1, Procedure compareAD2SQL-thbArchivedRecords, Line 7 The multi-part identifier "dbo.AD2SQL.LogonName" could not be bound.'
Here is the procedure code:
__________________________
Create PROCEDURE [dbo].[compareAD2SQL-thbArchivedRecords] AS IF not exists (select * from dbo.tblArchivedRecords where dbo.tblArchivedRecords.LogonName=dbo.AD2SQL.LogonName)

BEGIN  
   INSERT INTO tblArchivedRecords
      (FirstName, LastName, Fax, DepartmentID, Title, City, [State], EmailName, PHONE, HomePhone, DSID, LogonName)
   Select FirstName, LastName, Fax, DepartmentID, Title, City, [State], EmailName, PHONE, HomePhoneNumber, DSID, LogonName

FROM dbo.AD2SQL
END

ELSE BEGIN
  UPDATE tblArchivedRecords
  SET tblArchivedRecords.FirstName =AD2SQL.FirstName, tblArchivedRecords.LastName =AD2SQL.LastName FROM AD2SQL left outer JOIN tblArchivedRecords ON tblArchivedRecords.LogonName =AD2SQL.LogonName WHERE tblArchivedRecords.LogonName IS NULL END
 __________________

appreciate your help -Irina

HarterAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Harter,

As I understood it, you wanted to update the info if a corresponding record exists, and insert the info where
no corresponding records exist.  If that is what you want, then my suggestion should do the trick.

reb73: thanks for the vote of confidence :)

Regards,

Patrick
0
 
Patrick MatthewsCommented:
Create PROCEDURE [dbo].[compareAD2SQL-thbArchivedRecords] AS

BEGIN
      UPDATE tblArchivedRecords
      SET FirstName = a.FirstName, LastName = a.LastName
      FROM tblArchivedRecords t INNER JOIN
            AD2SQL a ON t.LogonName = a.LogonName

      INSERT INTO tblArchivedRecords (FirstName, LastName, Fax, DepartmentID, Title, City, [State],
            EmailName, PHONE, HomePhone, DSID, LogonName)
      SELECT a.FirstName, a.LastName, a.Fax, a.DepartmentID, a.Title, a.City, a.[State], a.EmailName,
            a.PHONE, a.HomePhoneNumber, a.DSID, a.LogonName
      FROM AD2SQL a LEFT JOIN
            tblArchivedRecords t ON a.LogonName = t.LogonName
      WHERE t.LogonName IS NULL

END
GO
0
 
HarterAuthor Commented:
where is 'if not exists part'
i want to insert into tblArchivedRecords records that are not there but in tblAD2SQL so
by using 'if not exists' I am comparing the two tables.
thanks
Irina
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
reb73Commented:
Looking at your code, one of either update or insert will succeed, so the if..else is optional.. Patrick's code will work just fine..
0
 
HarterAuthor Commented:
Reb73-thank you for the explanation
Patrick-thank you for the code

-Irina
0
 
HarterAuthor Commented:
-
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.