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.
Thanks.
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.
ASKER
New record:
- In Access Database (Origin Database)
- Not in SQL Database (Destination Database)
- In Access Database (Origin Database)
- Not in SQL Database (Destination Database)
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.