[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

834 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