Solved

sql server 2005, sql syntax, Merge

Posted on 2011-09-25
11
199 Views
Last Modified: 2012-05-12
Hi,
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
0
Comment
Question by:shmz
  • 5
  • 5
11 Comments
 
LVL 42

Expert Comment

by:dqmq
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?
0
 

Author Comment

by:shmz
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.

Thanks
0
 

Author Comment

by:shmz
ID: 36596764
table A and table B are already populated so B.ID_B and A.Id_A are already  available.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 42

Expert Comment

by:dqmq
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.    

0
 
LVL 50

Expert Comment

by:Lowfatspread
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.
0
 

Author Comment

by:shmz
ID: 36638556

okay,
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
0
 
LVL 42

Accepted Solution

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

OldStudentCourse
StudentID
CourseID

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
           


0
 
LVL 42

Expert Comment

by:dqmq
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.      
0
 

Author Comment

by:shmz
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?
0
 
LVL 42

Expert Comment

by:dqmq
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.  
0
 

Author Closing Comment

by:shmz
ID: 36899777
Thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading SQL 2005 Express to 2008 R2 Express 31 127
Updating statistics with error notification email in SQL server 4 109
Strange msg in the SSMS pane 13 58
Help Required 2 39
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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