Link to home
Start Free TrialLog in
Avatar of JJENSEN3
JJENSEN3

asked on

Import New Records From Access Table To SQL Database Table

I am trying to import only the new records from my Access database table to a Table on my SQL server using SSIS. The process errors out and halts the first time it encounters a duplicate PK. How can I import the new records only and ignore the records that already exist in my SQL table?

Thanks.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

For starters, give us your definition of 'new record'.  In most cases there is a datetime column that shows when the row was last updated.

SSIS has the Slowly Changing Dimension (SCD) transformation that can be used within a Data Flow component, which allows you to define source and target tables, define the primary keys, and how to behave if the primary keys are in source but not in target (i.e. INSERT), primary keys are in both source and target but other values changed (usually a 'Last Updated' datetime) (i.e. UPDATE), and if the primary keys are not in source but in target (either DELETE or leave alone).

The Pragmatic Works Task Factory toolset has a SCD component that is somewhat easier to use.

Be advised that the learning curve for pulling this off is pretty high.
Another easier way to pull this off, assuming you have an Access AutoNumber / SQL Server Identity field and are not dealing with updates, is to create a separate table that contains the last number that was imported from Access to SQL Server.  Then in your ETL process, modify it to only move rows WHERE id > {that stored value}.  Then after the ETL is complete update the field with the last id value moved.
Avatar of JJENSEN3
JJENSEN3

ASKER

New record:
-  In Access Database (Origin Database)
- Not in SQL Database (Destination Database)
What was ultimately done is to import my Access table that contained the mix of existing and new records into SQL (table1). From there I ran the query below against the table, inserting only the non matching records into the existing SQL table (table2).

Insert into table2
  select table1.*
  from table1 Left Join table2 On table2.Item_ID = table1.Item_ID
  Where table2.Item_ID Is Null;

This imported the records from table1 that did not exist in table2 into table2, which is what I needed.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes. I currently do that for larger tables. On this project I needed to go the other way, into SQL.  Thank you for your assistance.