sql server 2005, sql syntax, Merge

Posted on 2011-09-25
Last Modified: 2012-05-12
How can a MERGE be written using outer join, 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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 5 53
Sql Permission 6 59
How to place a condition in a filter criteria in t-sql? 12 68
How to simplify my SQL statement? 14 53
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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