Importing data into table(s) everyday

[pitbull]

I need to import data into table(s) everyday and if the record already exists require it to be updated with the new data. Each data file has approx 4,000 records and to make two passes at the file using Update and Append Query takes ages, does anybody know of an easier way.

 

[JimHorton]

If you're using a subselect for each query, it shouldn't be that bad, but it's the only real way, ie
First, for Update, use EXISTS, then for insert, use NOT EXISTS
Example of Insert:

Insert into Destination select Source.*from source where NOT Exists (select destination.ID from Destination where Destination.ID = Source.ID)
--Jim

 

[MichaelRed]

What is wrong with an Update query? If the record exists, it should be "Updated" to the new values, if it doesn't exist, it should be "updated" to a new record. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JimHorton]

Michael,
I'm not getting that one...what do you mean by 'update to a new record'?
--Jim

 

[MichaelRed]

O.K., If you don't already understand, this will taks a bit.

Here is a sample Table:


Count Name_Desc Price Weight Id
30 Chicker Skewers $8.99 1
18 Shrimp Wrapped in Bacon $5.33 2
40 Baja Bytes $9.99 3
120 Pizza Rolls $6.99 4
Buffalo Beestes $6.99 35 5
Chedar Poppers $9.99 48 6
40 Mexican Appitizers $9.99 7
Pu Pu Platter (5 Varities) $12.99 37 8
100 Assorted Hor's d'ouveries $14.99 9
45 Petite Quieche $14.99 10
Cheese Log $5.49 32 11
Cheeses Cubes $5.29 24 12
Pepperoini $3.99 16 13
Mexican 7 Layer dip $6.49 40 14
48 Bagel Bytes $6.99 7 15

So, make up or somehow create this as [BJs_Snacks]:

Copy the table (Structure ONLY) to [Bjs_Snacke_Update](while excusing the weirdness of my tabls naming / spelling / keyboard [NOT] skills.

Copy the following SQL into a query (Sql view (please)

UPDATE Bjs_Snacks LEFT JOIN Bjs_Snacke_Update ON Bjs_Snacks.Id = Bjs_Snacke_Update.Id SET Bjs_Snacke_Update.[Count] = [Bjs_Snacks].[Count], Bjs_Snacke_Update.Name_Desc = [Bjs_Snacks].[Name_Desc], Bjs_Snacke_Update.Price = [Bjs_Snacks].[Price], Bjs_Snacke_Update.Weight = [Bjs_Snacks].[Weight]
WHERE (((Bjs_Snacke_Update.Id) Is Null));


Run the query. the previously empty "... _Update" table now has all of hte recorde from the first table.

Delete a few records from the " ... _Update table" andd change some parts of other records n the original table. Wall-Wall-Bing-Bang (Purple people eaters UNITE!) look at the " ... _Update table. The deleted records are restored and the altered records are updated!


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JimHorton]

Michael,
Ah, yes, I see, very nice.
--Jim