Append Query Advice (1 Viewer)

[MattBaldry]

I tried 2 methods of copying the data from Sage STOCK_TRAN table, 174063 records.

Method 1: I use a query to make a local copy of the table then append this to my SQL table. Took around 1 minute 30 seconds to action both queries, then truncate the data.

Method 2: I use a query to append the data from the Sage table direct to my SQL table. I gave up timing this at 6 minutes.

My task now is to find a solution to my original request in making a "counter" to show which records have been imported.

A suggestion was to loop through the rows, 1,000 or so at a time. Would it be possible to do something like below?

Count the number TRAN_NUM field in the local table, in this case 174,063. Now, divide this by 10, 17,406 (ish) and run 10 loops.

After each loop I have a label that updates to the number that has been processed.

Loop 1 - 17,406 of 174,063
Loop 2 - 34,812 of 174,063

And so on.....

My reason for this is because now I have a handle on moving the Sage data to SQL, I will have 6 different imports to action. This table is the largest so the others should be shorter, but I would like a visual aid to the user that the data is being "imported".

We use an SQL based system called CIM50. Their MRP run takes around 20 minutes at a time, but they do have a visual aid to show the current progress.

Thank you in advance for any advice.

~Matt

 

[MajP]

I thought I covered this already, but yes you can.
I am assuming that the Tran_Num is always increasing. So if I have records in my destination table and records to be imported from my source table then the tran_Num to import are those in the source greater then the max Tran_Num in the destination.
1. Get the destination Max Tran_Num. "Max_Tran_Num"
2. Get the count of source records greater than Destination max Tran_Num. "Number of Records to Import"
3. Divide the count of records by 10 or some other percent. Lets call that the "Increment" = "Number of records to Import" /10.
4. Then loop from 1 to 10
"Select Top Increment from Source where Tran_Num > Max_Tran_Num"
Import records into destination
Get new "Max_Tran_Num" from destination
update your progress meter
loop

 

[MattBaldry]

I thought I covered this already, but yes you can.
I am assuming that the Tran_Num is always increasing. So if I have records in my destination table and records to be imported from my source table then the tran_Num to import are those in the source greater then the max Tran_Num in the destination.
1. Get the destination Max Tran_Num. "Max_Tran_Num"
2. Get the count of source records greater than Destination max Tran_Num. "Number of Records to Import"
3. Divide the count of records by 10 or some other percent. Lets call that the "Increment" = "Number of records to Import" /10.
4. Then loop from 1 to 10
"Select Top Increment from Source where Tran_Num > Max_Tran_Num"
Import records into destination
Get new "Max_Tran_Num" from destination
update your progress meter
loop

Thanks Maj,

I have never coded a loop before but I will find some codes and give it a go.

~Matt