I need to merge a table in an Access database with an
existing table in our SQL Server 2000 database. The
structure of the tables is the same. I found that the SQL
Server table didn't have an index defined, so I created a
primary index on the SQL Server table which matches the
primary index of the table in the Access database.

This is an incremental update so there is some overlap in
the data indexes, which is to be expected. I want the
rows from the Access database to replace the rows in the
SQL server table where the primary keys match. The data
is an incremental update for the SQl Server table.

I've looked at using the DTS Import/Export Wizard but I
can't set it up to do what I need. In the step "Select
Source Tables and Views" when I click on the Transform
button, there are 3 options: Create Destination table
(don't want that), Delete Rows in Destination Table (don't
want that), and Append Rows (don't want that).

Should I be using another tool to accomplish the merge I
need? Our previous DBA left no documentation behind and
I'm trying to accomplish this with no training. Your
patience and assistance would be appreciated.

Ok

There are a few way to skin this cat but here is one way

Create a linked server from the SQL Server to the Access DB

You can then query the Access DB easily through TSQL (OPENQUERY)

You then need to join the tables together based on matching key values and
issue UPDATE statements

To get the new rows from the Access DB you could INSERT into the SQL Server
table where the key value is in Access but not in SQL Server

--

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Rick Young" <fjyoung (AT) forsythco (DOT) com> wrote