Compare 2 tables in sql 2005 and append if does not exist
Posted on 2009-02-23
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)
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
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