Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
whill96205
Starting Member
46 Posts |
 Posted - 2004-07-07 : 14:54:45
|
| 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; and2) 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 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-07 : 19:30:41
|
| Your best bet would be to create a staging table that you can import your Access and Excel data into. You can then run a series of queries to update your production database.So, in answer to your first question, the query might look something like this: INSERT INTO INVENTORY (field1, field2 field3) SELECT fields.... FROM Inventory_Stage WHERE LotID IN (SELECT LotID FROM LOTS)For your second question: INSERT INTO Lots (LotNum, Description, Location) SELECT LotNum, Description, Location FROM Lots_Staging WHERE LotNum NOT IN (SELECT LotNum FROM Lots)That should do the trick..HTH,Tim |
 |
|
|
|
|
|
|
|
Merging Tables: How?