Go Premium for a chance to win a PS4. Enter to Win

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

Access/SQL Comparing records against Composite Primary Key

I have a table in SQL Server with a number of fields, i have created a composite PK of LoanNumber, BorrowerName, LenderNumber; I need to compare a new data set that contains these and other fields to determine if there are any new PK on the new data set, which will then be added to the original table.

Thank you!



Keyword Tags:
VBA, SQL, SQL Server, Access, VB
0
Charlesdavid Allen
Asked:
Charlesdavid Allen
1 Solution
 
Ryan ChongCommented:
>>I need to compare a new data set that contains these and other fields to determine if there are any new PK on the new data set, which will then be added to the original table.

first, where you want to compare these records? in Ms SQL Server,Access, etc? reason being there are different approaches we can do this.
0
 
Pawan KumarDatabase ExpertCommented:
Then you can use LEFT JOIN with the two table to get the new records.


Which DB you are referring ? SQL Server ?
0
 
Charlesdavid AllenAuthor Commented:
comparing access table (comma delimited, imported, error checked and formated) in code. has same field names but no PK as the SQL Server table (ODBC Linked).
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
PatHartmanCommented:
Although joining the Access table to the SQL Server table is possible, it is inefficient since Access will send a request to download the ENTIRE SQL Server table and perform the join locally.  If the SQL Server table isn't large, then it won't be a problem but once you get to more than 100,000, you probably want to do something different.  Uploading the Access data to a temp table in SQL Server might prove to be more efficient if the Access table is very small and the server side table is very large.  Then you can do the left join in SQL Server.

The left join will return the "new" records.
0
 
Charlesdavid AllenAuthor Commented:
left join on each field = corresponding field?
0
 
Charlesdavid AllenAuthor Commented:
Pat that is to say,

Loaded the access able to a temp table in SQL called tempNewLoandata

SELECT *
FROM tempNewLoanData
LEFT JOIN MasterLoanData
ON tempNewLoanData.LoanNumber = MasterLoanData.LoanNumber AND   tempNewLoanData.BorrowerName = MasterLoanData.BorrowerName AND tempNewLoanData.LenderNumber = MasterLoanData.LenderNumber

?
0
 
PatHartmanCommented:
SELECT *
FROM tempNewLoanData
LEFT JOIN MasterLoanData
ON tempNewLoanData.LoanNumber = MasterLoanData.LoanNumber AND   tempNewLoanData.BorrowerName = MasterLoanData.BorrowerName AND tempNewLoanData.LenderNumber = MasterLoanData.LenderNumber

WHERE MasterLoanData.UniqueID Is Null;

You need a where clause.  If you don't have an autonumber PK, then just use one of the join fields instead.
1
 
Charlesdavid AllenAuthor Commented:
Thanks for the hand as always Pat.  Worked well; My brain was not firing on all cylinders that morning.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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