Add Many-to-Many all at once
-
I have a construct that looks like this:
tblItemMaster (ItemID int Identity, other columns);
tblTPItemMaster (tpItemID int Identity, other columns);
tblXrefItemtpItem (ItemID, tpItemID, Priority int=1)
It is a many-to-many construct.
I want to write a bit of SQL code to create a new set. I have the fields I need in a view. The view matches an imported table against existing tblTPItemMaster entries to isolate new adds. The strategy is to create a new TPItem, then a new Item, then a new XREF linking them up. Doing it one item at a time seems straightforward:
Start a transaction;
Add the new TPItem, get its @@identity;
Add the new Item, get its @@identity;
Add the new XREF;
Commit the transaction;
But in SQL, we're supposed to try to do everything with sets, right? I can certainly provide a set of transactions that need adding. I don't know how to accomplish the above steps using sets. You guys got the answer on the tip of your tongues? (or keyboards?)
Jim
-
What does the structure of the imported table and the view look like? I'm thinking depending on their definition, you could do your set based inserts either by using MERGE statements or INSERT..SELECT
-
Using the OUTPUT clause is a common pattern for something like this.
-
Rick Krueger (12/4/2012)
Using the OUTPUT clause is a common pattern for something like this.Only if you intend to store the parent ID in the child table. Otherwise, it's a disconnect result set being returned and does not provide the ability to reliably make the bridge table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@ Jim,
I'm sure there are other ways to do this but this is about the only place that I actually use GUIDs. I build a sacraficial GUID column in the child table so that can use OUTPUT to match staged input rows to the resulting rows to build the many-to-many bridge table rows. That way I can do a very effective match on the IDENTITY columns to build the bridge table from OUTPUT.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I had a similar idea, basically using the identity column from the input table, I can insert that value into a spare column in each of the two item masters, then match them up to create the xref table entries, then delete the input identity value from the two item masters. I suspect it's the same as your GUID idea....
Thanks.
Jim
-
JimS-Indy (12/5/2012)
@Jeff,I had a similar idea, basically using the identity column from the input table, I can insert that value into a spare column in each of the two item masters, then match them up to create the xref table entries, then delete the input identity value from the two item masters. I suspect it's the same as your GUID idea....
Thanks.
Either way will work. I guess my question would now be, do you really need a man-to-many relationship for this? If not, just leave the IDENTITY in the child table and eliminate the bridge table. I know that'll make some of the folks in favor of normalization cringe a bit but it is a worthwhile simplification.
The reason why I used the GUID instead of an IDENTITY is so that I don't have to put an extra lock on the system to do the deletes on busy systems.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This is a list of Part numbers (Items) linked with their (zero or more) Sources of supply (vendors, etc.) An item could have half a dozen sources, though typically zero or one.
I'll post the code when I'm done, but I've got it doing almost everything now in 7 seconds, using GUIDs.
Jim
-
JimS-Indy (12/5/2012)
This is a list of Part numbers (Items) linked with their (zero or more) Sources of supply (vendors, etc.) An item could have half a dozen sources, though typically zero or one.I'll post the code when I'm done, but I've got it doing almost everything now in 7 seconds, using GUIDs.
That's definitely a many-to-many requirement! 🙂
When you say "everything now in 7 seconds", what does the "everything now" mean and how many rows are you talking about?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So, all of this runs inside an SSIS package. When the user extracts a TSV file from the mothership and places it in a folder, the SSIS package extracts and transforms the import table into the "transaction" table (comprehensive table...contains ALL items offered by a given source), then...
Step 1, add all new items to the TPItem table
Step 2, add all the same new items to the Item table
Step 3, link all the new items in the xref table
Step 4, update all the existing TPItems with any changes from the transaction table (this one takes the longest...)
Step 5, clean up all the GUIDs
....
Step 6 (not done yet): Note all the new items in a mail message
Step 7 (not done yet): Note all the updates in a mail message
Step 8 (not done yet): Note any items in the TPItem Master that are not in the transaction table in an email (meaning they are no longer supplied by this source, so they need have their TPItem removed from the corresponding Item (defaults to send to purchasing for sourcing). I will probably remove the TPItem at this time...need to consult with the user.
Jim
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply