I am creating a SQL table from a couple of different sources.

The first data source is an Excel spreadsheet. The spreadsheet tracks
all the attributes of LOTs (description, location, seller, a unique
LotNum, etc.) This spreadsheet is historical - that is, it includes
records for LOTs that have been sold through my client, as well as
LOTs that may still be on auction. At the moment, this spreadsheet is
fairly complete, but the Location column is nearly empty for all
records.

The second data source is a small Access 2000 database that is being
used to conduct an inventory of LOTs sitting in my client's warehouse.
This database will contain unique LotNum, as well as description and
location. The Access db is being used for this task for various
reasons.

My question revolves around merging these two sources into a single
SQL table. Many, but not all, LotNum values that will appear in the
Access database will already exist in the spreadsheet. So, let's
assume I import all the Excel data into a SQL table first. Now, I
want to import the Access data and have it do two things:

1) Import *only* the Location value for LOT records that are already
in the SQL table; and

2) Import complete records (LotNum, Description, Location) for LOTs
that are *not* already in the SQL table.

I'm thinking this is more of a "merge" than an "import", but am
baffled as to how to do it. Can it be done in SQL? I'd rather avoid
any kind of manual intervention (data entry) since there are upwards
of 3000 items whose locations are being verified in the warehouse.

Thanks,

Whill,

replication doesn't really solve this type of issue, at least it wouldn't be
my first choice of technology. The simplest method is to create a linked
server to the access database. After that's done, to solve your two issues:

(1) create an update statement which links to the imported excel data:

UPDATE importedExcelTable
SET Location = AccessLinkedServer...AccessTable
FROM importedExcelTable, AccessLinkedServer...AccessTable
WHERE importedExcelTable.lot = AccessLinkedServer...AccessTable.lot

(2) Use a subquery to get new records:

INSERT INTO importedExcelTable(LotNum, Description, Location)
SELECT (LotNum, Description, Location)
FROM AccessLinkedServer...AccessTable
WHERE AccessLinkedServer...AccessTable.lot NOT IN (select lot
from importedExcelTable)

HTH,

Paul Ibison

Paul, thanks for the suggestion. I was trying to figure out the UPDATE
syntax late yesterday.

Here's a newb question for you: what do you mean by "create a linked
server to the access database", and how do you do it?

--Whill

[quoted text, click to view]