SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add Many-to-Many all at once


Add Many-to-Many all at once

Author
Message
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 444
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
Chris Harshman
Chris Harshman
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4840 Visits: 3949
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
Rick Krueger
Rick Krueger
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 691
Using the OUTPUT clause is a common pattern for something like this.



Rick Krueger

Follow @dataogre
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85160 Visits: 41077
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85160 Visits: 41077
@ 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 444
@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.

Jim
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85160 Visits: 41077
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 444
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85160 Visits: 41077
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 444
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search