Importing only new data from Access to SQL Server

[IS300]

Hello,

Currently, we have an Intranet running SQL server as the primary source of data.

We also have Access databases that hold employee information. We are still trying to phase out Access, but do not want to do it all at once.

I want to be able to import this data on a nightly basis or so, so that the new data updated in Access appears on the Intranet.

The problem is that it will not import the rows, as it cannot insert a duplicate key.

Is there a way to set it up so that it imports only new information into existing rows? Or insert a new row if it doesn't exist already? It appears to try to import everything, and bombs since it won't insert two of the same primary keys.

Thanks,

 

[JayKusch]

If possible, which is usually not the case, truncate the SQL Server table prior to inserting the new updates. Thus you are asured of getting all of the Updated records in there as well as the new created or deleted records.

If that is not an option, You may have to do this in 2 passes.

On the first pass, you can create a NOT EXISTS statement and insert the records from Access that are not found in the SQL Server table. That takes care of new records.

May also want to run the reverse in seeing what SQL Server records are present that are not in Access. This will then give you a set of records to delete from SQL Server.

Now for the tricky part. There has to be some type of common link between the Access records and the SQL records. An EmployeeID or something similiar. You then need to have a field, bit maybe, on your Access record that can be toggled on/off when changes/updates happen. Your nightly process would then select all the records w/ the flag turned on and update the SQL record that corresponds based on the common ID field.

Hope this helps!

Thanks

J. Kusch

 

[rasETL]

I agree with most of what JayKusch wrote...

However, you first need to determine which data is the
most current. If updates are taking place during the day
in both databases, you need to handle it differently than
if the Access database has all changes/additions and the
SQL Server database is query only.

Best scenario - all inserts/updates/deletions are in the
Access database. Then TRUNCATE the Table if allowed, or,
if the Table is small a DELETE FROM could also run -
prior to the copy task.

If you provide a little more detail...

 

[JayKusch]

resanders is correct!

My explaination was based on the assumption that the Access DB was the source DB that ALL modifications/additions/deletions were occuring. The SQL Server was to be a target server where the changes were to be replicated too and NOT from.

Thanks

J. Kusch