Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access/SQL Comparing records against Composite Primary Key

Posted on 2016-09-19
8
Medium Priority
?
84 Views
Last Modified: 2016-09-25
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
Comment
Question by:Charlesdavid Allen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41804524
>>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
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41804617
Then you can use LEFT JOIN with the two table to get the new records.


Which DB you are referring ? SQL Server ?
0
 
LVL 1

Author Comment

by:Charlesdavid Allen
ID: 41804676
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

Expert Comment

by:PatHartman
ID: 41805125
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
 
LVL 1

Author Comment

by:Charlesdavid Allen
ID: 41805309
left join on each field = corresponding field?
0
 
LVL 1

Author Comment

by:Charlesdavid Allen
ID: 41805321
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
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41805335
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
 
LVL 1

Author Closing Comment

by:Charlesdavid Allen
ID: 41815040
Thanks for the hand as always Pat.  Worked well; My brain was not firing on all cylinders that morning.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question