Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Add Many-to-Many all at once
Add to briefcase

Add Many-to-Many all at once Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
JimS-Indy
Posted Tuesday, December 04, 2012 1:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317
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
Chris Harshman
Posted Tuesday, December 04, 2012 3:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 1,564, Visits: 1,719
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
Rick Krueger
Posted Tuesday, December 04, 2012 3:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 262, Visits: 576
Using the OUTPUT clause is a common pattern for something like this.


Rick Krueger

Follow @dataogre
Post #1392732
Jeff Moden
Posted Tuesday, December 04, 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 @ 8:14 AM
Points: 32,910, Visits: 26,802
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1392744
Jeff Moden
Posted Tuesday, December 04, 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 @ 8:14 AM
Points: 32,910, Visits: 26,802
@ 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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1392746
JimS-Indy
Posted Wednesday, December 05, 2012 8:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317
@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
Jeff Moden
Posted Wednesday, December 05, 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 @ 8:14 AM
Points: 32,910, Visits: 26,802
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1393264
JimS-Indy
Posted Wednesday, December 05, 2012 5:26 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317
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
Jeff Moden
Posted Wednesday, December 05, 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 @ 8:14 AM
Points: 32,910, Visits: 26,802
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1393317
JimS-Indy
Posted Thursday, December 06, 2012 7:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.