[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1078
  • Last Modified:

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

0
Harter
Asked:
Harter
  • 3
  • 2
1 Solution
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
HarterAuthor Commented:
Reb73-thank you for the explanation
Patrick-thank you for the code

-Irina
0
 
HarterAuthor Commented:
-
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now