What's the best way to clear tbl and then append to it? 1

[KerryL]

I have a situation where each day I need to re-populate a master table with data from multiple databases. (Yes, perhaps replication would've been best but I inherited this mess and it's too late now.) I'm not sure the best way to do this, so I'm looking for some advice from the experts here.

Let me try explaining what I need to do by using simple example names.

Master database = dbMaster
Master table = tblServices
Site databases = dbSite01 thru dbSite10
Site tables = tblServices01 thru tblServices10

Reporting will be done out of dbMaster.tblServices, so a data refresh will need to be done on a daily basis. IOW, each day dbMaster.tblServices will need to be cleared and then dbSite01.tblServices01 thru dbSite10.tblServices10 all need to be appended to it.

What's the best way to make this happen? A simple append query in dbMaster? If so, how do I clear tblMaster first? And can any of this be automated and scheduled so it doesn't have to be done manually?

Thanks in advance for your suggestions,
Kerry

 

[GingerR]

Hi. You can just use a DELETE query to clear the master table. Then you can either use 10 append queries, or one UNION append query to append the records from the 10 other tables to the main one.

I don't see how replication would have been best. I do this kind of thing all the time...

Anyhow, yes you can automate it. Look up Autoexec macro. Also look up Windows Task Scheduler. If you are running on Windows OS, you can schedule the db to open at a certain time (i.e. 4:00 am). When a DB opens, it will run any macro called "Autoexec" automatically. The simplest thing is to just list your queries in there.

So check out those topics and let us know how it goes!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

Thank you Ginger, I appreciate your advice.
I'll give it a try and let you know what happens.

 

[KerryL]

Okay, I'm confused.

I successfully created the delete query. But how do I append a table from a separate database onto a table in the database I'm creating the query in? And is there a way to simply append the entire table, or do I have to do it field by field?

 

[GingerR]

Have you linked to the table? Use FILE-GET EXTERNAL DATA-LINK TABLES.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

Yes, I've linked to the tables in 3 separate databases. I just can't seem to figure out how to append the entire table instead of doing it field by field.

 

[traingamer]

Have you tried:

INSERT INTO tblServices
SELECT 
FROM tblServices01;

and so on...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

No, I hadn't tried that...thanks Greg. I assume I simply add that into the SQL window of a query?

In order for that to work do both tables need to be identical? Same number of fields, same field names, same data types, etc?

If I do it that way can I put all 10 inserts into the same query?

 

[traingamer]

I assume I simply add that into the SQL window of a query?

Yes

In order for that to work do both tables need to be identical? Same number of fields, same field names, same data types, etc?

Yes

If I do it that way can I put all 10 inserts into the same query?

I don't think so. But someone else may have a better idea.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[GingerR]

I just can't seem to figure out how to append the entire table instead of doing it field by field.

If the field names are not identical, you have to make an append query field-by-field. Not sure why this is difficult for you? You just put each field in the query grid, then select the cooresponding field name to put it in.

If all fields in all 10 linked tables are in the same order and are the same data type as the table you're appending to, you can try

Select * from Table1 UNION
Select * from Table2 UNION
Select * from Table3;

etc

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

I've kept getting a syntax error with this one:

INSERT INTO ServicesMaster
SELECT
FROM ServicesCaro;


However, this format worked like a charm:

SELECT * FROM ServicesWayne UNION
SELECT * FROM ServicesCaro UNION
SELECT * FROM ServicesLansing;


Thank you very much, Ginger!!