Transferring GL20000 to Reporting Company GL20000

[klewis10367]

We run a DTS every 2 hours on our GL20000 from our Live Company to a Daily Reporting Company(2 different databases). This process has slowly becoming longer and longer, does anyone know of or have any ideas of a faster way to get the same info(data integrity intact) over to the reporting company? I forgot, after we run the DTS we also have to reconcile the GL.

 

[jymm]

have you thought about modifying the DTS to only copy the new accounts that were added/modified???

I can help out with the SQL if need be, but for my reporting company (similar set up I am sure) I do not take all of the fields

 

[klewis10367]

I actual kinda of did that already, I took just the range of accounts that I needed ( through a view) and did it. It shortened it quite a bit, but i like the idea of just taking the appropriate fields. Which ones do you use?

 

[jymm]

I actually pull the 'Chart of Accounts' from GL00100. I only take the index, our account segments, description, Mgmt class. From GL20000 I grab JRNLEntry, SourceDoc, Description, Transaction Date, Trans Source, Credit & Debit Amounts and the Dex. I actually also pull the same/similar information from GL10111, GL10110 and GL30000 so that I can get a clear picture of the start of the year and previous years.

If you have the dex ID in the report table you can use that (I have found it to be unique in this instance)

Do something like this (YMMV)

Insert into myreportdb..myGLTransactions
Select (all your reporting fields here)
from DynamicsCompany..GL200000 left join
myreportdb..myGLTransactions on DynamicsCompany..GL200000.dex_row_id = myreportdb..myGLTransactions.dex_row_id
Where myreportdb..myGLTransactions.dex_row_id is null

ideally this will pull only those dex_row_ids from the GL20000 that are not already in the report db --- now I free handed this one from memory, so again - YMMV --- try just running the select part (take out the insert) and see what you get...