Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


sql server 2005, sql syntax, Merge

Posted on 2011-09-25
Medium Priority
Last Modified: 2012-05-12
How can a MERGE be written using outer join,...to populate a M:M table?

Let say I have:
 TableA           (1:M)           TableManytoMany           (M:1)          Table B
 ID_A (pk)                           AutoIDPK                                           ID_B (pk)
                                           ID_A (fk)
                                           ID_B (fk)

Thanks in advance
Question by:shmz
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 42

Expert Comment

ID: 36595567
Insert into AB
   select A.ID_A, B.ID_B from TableA A, TableB B
   where ????????????????

Without the WHERE clause, every A will be assigned to every B!!!!   My guess--that is probably not what you really want.  The purpose of the WHERE clause is to declare which A's and which B's are related.

FWIW, do you think the AutoIDPK on the many-many table may be a little over-the-top?  Does it hide an important business rule:  that the combinations of A and B in the many-many table should be unique?

Author Comment

ID: 36596761
Please read the question. I have not said to place where or not and not sure why you are asking this question.
Please do not worry about the table structure. All I need is how to write the query.


Author Comment

ID: 36596764
table A and table B are already populated so B.ID_B and A.Id_A are already  available.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 42

Expert Comment

ID: 36600000
>I have not said to place where or not and not sure why you are asking this question.

I provided the WHERE clause because my query requires it to produce meaningful results.  I mentioned it precisely because you did not.  But feel free to ignore it and you will get a cartesion join of TableA and TableB.

>table A and table B are already populated so B.ID_B and A.Id_A are already available.

Right. The purpose of a M:M table is to express a relationship between some rows in TableA with some rows in TableB.  To populate it in a useful way, it's important to know which rows are related.  There is not enough information in TableA and/or TableB to convey the relationship (if there were, then you wouldn't need the M:M table).  So, that information needs to come from somewhere else:  specifically, the WHERE clause.

>All I need is how to write the query.
Here it is again:

Insert into TableManyToMany
   select A.ID_A, B.ID_B from TableA A, TableB B 
   --  where  ??????????????

Open in new window

I purposely disregarded your request for an OUTER join because it's sort of incongruent with populating a M:M table. On the other hand, perhaps I overlooked something important and do not really understand your goal.    

LVL 50

Expert Comment

ID: 36600442
you cant join (any type inner or outer) if you don't have a common set of key columns on each side of the join....

so as you have specified you cannot outer join table A and Table B

all you can do is a cross product / cartesian join against the tables...

please reconsider and tell us exactly what the situation is that you are trying to solve.

Author Comment

ID: 36638556

Lets call tableA = student
LetCall tableB = course

Lets say I like to import data from an old none relational database to a new relational database.

Student                    StudentCourses                Courses
St_AutoID (pk)         SC_AutoID                         C_AutoID (pk)
StudentID                 ST_AutoID (fk)                   Desc
Name                       C_autoID (fk)                      CourseID
The Courses table is already populated with a list of available courses. It is a table lookup. So C_AutoID is available.
Lets say I import all student into the table Student. so st_AutID is also available.

In old database I did not have auto id, the StudentID was related to the courseID.

I need to check which St_AutoID is related to which CourseID and populate the StudentCourse table.

(I know it is a more detailed version of original questions) . I understand that WHERE clause is required to get the query working. I just did't know that I should mention it. :)

Many Thanks in advance
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 36648309
To populate the StudentCourses table, you need the old data that relates student and courses.  Since it's non-relational, I suggest you import that data into a table that looks like this:


Then run this:

Insert into StudentCourses (St_AutoID, C_AutoID)
  Select s.St_AutoID, c.C_autoID from OldStudentCourse O
          inner join Student s on s.StudentID = o.StudentID
          inner join Courses c on c.CourseID = o.CourseID

LVL 42

Expert Comment

ID: 36653862
I should know better then to raise the controversy (especially since I've already got my hand slapped once), but I can't stand remaining silent in the face of seemingly nonsense AutoID's. This is a forum for you to get help and all of us to learn, after all. I'm especially concerned about the potential to lose uniqueness over the natural keys.      

Author Comment

ID: 36673115
Sorry, didn't mean any disrespect.

The form interface that does the validation is forcing uniqueness.

I agree that the autoId like  SC_AutoID  may not be necessary. The problem is that if something like courseID changes over the time, then it will cause extra works over the data entry (M:M) tables. (given the nature of this db which is not really students and courses)

Many thanks for the query, works perfect.

 any chance of getting it working using Merge?
LVL 42

Expert Comment

ID: 36710792
>The form interface that does the validation is forcing uniqueness.
That's good; I advocate trapping such errors in the form interface.  But in many years' experience, I've learned that it is not good enough.  I've learned to ALWAYS, ALWAYS (and I don't use that adverb very often) declare a unique key constraint on the natural key when adopting a surrogate key.  Besides, the index generated to enforce that constraint will be useful in it's own right.

>(given the nature of this db which is not really students and courses)

I see. The nature of the db (or we say the nature of the business being represented) is indeed the driving force.  I'll relax and trust your judgement about whether the added complexity of the surrogate key is beneficial at the end of the day.  

Author Closing Comment

ID: 36899777

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question