[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

sql server 2005, sql syntax, Merge

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
  • 5
  • 5
1 Solution
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?
shmzAuthor Commented:
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.

shmzAuthor Commented:
table A and table B are already populated so B.ID_B and A.Id_A are already  available.
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

>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.    

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.
shmzAuthor Commented:

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
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

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.      
shmzAuthor Commented:
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?
>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.  
shmzAuthor Commented:

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now