I am attempting to copy the entire record(Quote - Table1) and related line items (Quote Line Item - Table2) into the project table (same field names). I want a copy of the record so that the original quote can be referenced if needed and because when the project is worked other items can and quite often are added. Any help would be greatly appreciated. I have a feeling I am just missing something.
If I use the set variables script step I am sure I will be able to get the quote data but not the quote line items.
Quote Table Quote Line Items
pk_QuoteID ------------------< fk_QuoteID
I designed the quote area of the system with the ability to add as many line items as needed.
A couple of things I was thinking about doing was creating a found set on the Quote Line items since they all have a FK with the quote ID but my concern is that as the database gets larger it will slow down greatly. There is a summary field in there that counts the line items, so I would be able to loop thru them and compare the current line item number to the total on the quote.
Could an SQL statement of some sort help with this process? (You can roll your eyes now hehe)