Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add Many-to-Many all at once Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 1:53 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1392689
Posted Tuesday, December 4, 2012 3:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 1,849, Visits: 2,010
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
Post #1392719
Posted Tuesday, December 4, 2012 3:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:24 PM
Points: 262, Visits: 681
Using the OUTPUT clause is a common pattern for something like this.



Rick Krueger

Follow @dataogre
Post #1392732
Posted Tuesday, December 4, 2012 5:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392744
Posted Tuesday, December 4, 2012 5:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 36,752, Visits: 31,208
@ 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392746
Posted Wednesday, December 5, 2012 8:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
@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
Post #1393042
Posted Wednesday, December 5, 2012 4:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1393264
Posted Wednesday, December 5, 2012 5:26 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1393278
Posted Wednesday, December 5, 2012 10:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1393317
Posted Thursday, December 6, 2012 7:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1393525
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse