January 29, 2014 at 6:49 am
The table is being populated manually at the moment, I have to use a trigger so that when new data gets inserted, it will automatically move over to the new table.
January 29, 2014 at 6:58 am
why not just create a procedure that finds what has not been migrated yet?
either via a join so that it is a perfect match, but probably resource intensive, or by tracking the highest invoice moved so far?
--LEFT join will find accidentally skipped records
--INSERT INTO PAT_AJS.dbo.AJS_Client_Invoice
SELECT
IL.INVOICE_ID,
IH.Invocie_DATE,
IH.Case_ID,
IH.Actor_ID,
IL.Invoice_Line_Text,
IL.Invoice_Amount,
B_L_SEQ_NUMBER,
B_L_AMOUNT,
BL.WORK_CODE_ID
FROM INVOICE_HEADER IH
INNER JOIN INVOICE_LINE IL
ON IH.Invoice_ID = IL.Invoice_ID
INNER JOIN BUDGET_LINE BL
ON B_L_INVOICE_NUMBER = IH.INVOICE_ID
LEFT OUTER JOIN PAT_AJS.dbo.AJS_Client_Invoice RECS
ON IH.Invoice_ID = RECS.Invoice_ID
WHERE RECS.Invoice_ID IS NULL
--find the highest record migrated so far:
declare @CurrentHighestRecord int
SELECT @CurrentHighestRecord = MAX(INVOICE_ID) FROM PAT_AJS.dbo.AJS_Client_Invoices
--Get ALL records that have not yet been migrated.
--INSERT INTO PAT_AJS.dbo.AJS_Client_Invoice
SELECT
IL.INVOICE_ID,
IH.Invocie_DATE,
IH.Case_ID,
IH.Actor_ID,
IL.Invoice_Line_Text,
IL.Invoice_Amount,
B_L_SEQ_NUMBER,
B_L_AMOUNT,
BL.WORK_CODE_ID
FROM INVOICE_HEADER IH
INNER JOIN INVOICE_LINE IL
ON IH.Invoice_ID = IL.Invoice_ID
INNER JOIN BUDGET_LINE BL
ON B_L_INVOICE_NUMBER = IH.INVOICE_ID
WHERE IL.INVOICE_ID > @CurrentHighestRecord
Lowell
January 29, 2014 at 7:03 am
crazy_new (1/29/2014)
There are multiple line in the Invoice line table with the same ID, that's why I joined the Invoice ID and Invoice header.I have to move them across from the one table to the other as soon as they are inserted(a new invoice_header_id is created), so every time a new max id comes in, it will take everything with that same ID and move it to the new table.
So that's why I only check for the max ID.
I'm not great with sql yet so that's how I thought it would be best.
In most systems I have worked with, Header is created first, then one to many detail lines.
Detail lines can also be added at a later date.
And Invoicing go through statuses - create, print, etc.
The more details you give, and the more directly you answer questions that are asked, the better the quality of advice you are likely to get.
As you could see from some of the questions, why carries some weight, not just what you want to do.
Even such things as a record is created, but then deleted, might be something that matters.
Well beyond the scope of what you asked, but likely part of the overall picture.
When you have a header file that is unique, and 2 detail files that can be 1 to 1 or 1 to many, care needs to be taken.
Results can be mixed.
See Cross Join.
January 29, 2014 at 7:26 am
Lowell (1/29/2014)
why not just create a procedure that finds what has not been migrated yet?either via a join so that it is a perfect match, but probably resource intensive, or by tracking the highest invoice moved so far?
--LEFT join will find accidentally skipped records
--INSERT INTO PAT_AJS.dbo.AJS_Client_Invoice
SELECT
IL.INVOICE_ID,
IH.Invocie_DATE,
IH.Case_ID,
IH.Actor_ID,
IL.Invoice_Line_Text,
IL.Invoice_Amount,
B_L_SEQ_NUMBER,
B_L_AMOUNT,
BL.WORK_CODE_ID
FROM INVOICE_HEADER IH
INNER JOIN INVOICE_LINE IL
ON IH.Invoice_ID = IL.Invoice_ID
INNER JOIN BUDGET_LINE BL
ON B_L_INVOICE_NUMBER = IH.INVOICE_ID
LEFT OUTER JOIN PAT_AJS.dbo.AJS_Client_Invoice RECS
ON IH.Invoice_ID = RECS.Invoice_ID
WHERE RECS.Invoice_ID IS NULL
--find the highest record migrated so far:
declare @CurrentHighestRecord int
SELECT @CurrentHighestRecord = MAX(INVOICE_ID) FROM PAT_AJS.dbo.AJS_Client_Invoices
--Get ALL records that have not yet been migrated.
--INSERT INTO PAT_AJS.dbo.AJS_Client_Invoice
SELECT
IL.INVOICE_ID,
IH.Invocie_DATE,
IH.Case_ID,
IH.Actor_ID,
IL.Invoice_Line_Text,
IL.Invoice_Amount,
B_L_SEQ_NUMBER,
B_L_AMOUNT,
BL.WORK_CODE_ID
FROM INVOICE_HEADER IH
INNER JOIN INVOICE_LINE IL
ON IH.Invoice_ID = IL.Invoice_ID
INNER JOIN BUDGET_LINE BL
ON B_L_INVOICE_NUMBER = IH.INVOICE_ID
WHERE IL.INVOICE_ID > @CurrentHighestRecord
Wouldn't an Exception Join (NOT EXSISTS) actually be better?
Although a real gap could also be record updated.
Or maybe that never happens. 😀
Bottom line - spend the time to get it right.
I would hate to be making up data, especially in something critical to the Business.
January 29, 2014 at 7:30 am
Greg Edwards-268690 (1/29/2014)[hr
Wouldn't an Exception Join (NOT EXSISTS) actually be better?
Although a real gap could also be record updated.
Or maybe that never happens. 😀
Bottom line - spend the time to get it right.
I would hate to be making up data, especially in something critical to the Business.
yeah, an exists would certainly be better than my first example; excellent point.
I'm pretty sure that his current procedure requires data to exist in three tables(inner joins) before it gets moved over, so for me that puts the trigger out the window.
Lowell
January 29, 2014 at 7:52 am
Lowell (1/29/2014)
Greg Edwards-268690 (1/29/2014)[hr
Wouldn't an Exception Join (NOT EXSISTS) actually be better?
Although a real gap could also be record updated.
Or maybe that never happens. 😀
Bottom line - spend the time to get it right.
I would hate to be making up data, especially in something critical to the Business.
yeah, an exists would certainly be better than my first example; excellent point.
I'm pretty sure that his current procedure requires data to exist in three tables(inner joins) before it gets moved over, so for me that puts the trigger out the window.
Pretty sure?
If the header is created, then the detail lines later, there will surely be issues.
Maybe since the bulk of my exposure has been to systems where this would be a normal workflow, I am way overthinking things.
User creates header, presses enter.
Header is created, takes user to screen to enter details.
So header exists, but no detail lines at that instant.
Not trying to be a pain, but would rather the poster verify that workflow just to be sure.
It might save them from troubleshooting issues and rework down the road.
After all, he admits being new to SQL, and we only have bits and pieces of the overall picture.
January 29, 2014 at 11:12 pm
The end user would insert all the details before the invoice is created, when he creates a preview the invoice show up on the screen with an invoice number starting with a 9. He can cancel it if he forgot something, then add the lines and recreate the preview, when he is happy with it he will finialize the invoice, then the invoice number will change to something starting with a 1. So all the right info will be there when it goes into the DB. Thats why i select it starting with a 1.
January 30, 2014 at 4:58 am
crazy_new (1/29/2014)
The end user would insert all the details before the invoice is created, when he creates a preview the invoice show up on the screen with an invoice number starting with a 9. He can cancel it if he forgot something, then add the lines and recreate the preview, when he is happy with it he will finialize the invoice, then the invoice number will change to something starting with a 1. So all the right info will be there when it goes into the DB. Thats why i select it starting with a 1.
That explains some of the odd appearing logic in the original query.
What you describe sounds like the header might be created more in line with what I described, but you are not interested in it yet.
Instead of changing the key (assuming ID), I've usually seen this taken care of with a status.
Then there would not be 999... and 100.... records that are not connected.
Works for you, but I don't see this as a very good design.
Although it may appear to be a long ways off, that scheme will break at some point.
If the records are inserted with 9, and then a new record inserted with 1, can't the trigger just look for the 1's?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply