Filling many to many rel table using Select statement

indian143

Hi All, I have Table A, TabB and TableC, I need to fill TableC by using Select statement and join on TableB and TableA two times. Because TableC is a many to many relationship table on TableA and TableB.

TableA(Id, Name, Desc)
TableB(Id, TableAId, AnotherTableAId, TableAName, AnotherTableAName)
TableC(Id, TableAId, AnotherTableAId, TableAName, AnotherTableAName)

Now the problem is TableA fills the new Data and TableC should be synced to TableB but should have new Ids from TableA and same TableAName, AnotherTableAName values from Old TableB rows. If I run as below, the rows aren't coming correctly, some times more rows than in TableB or some time less rows than in TableB. Here is how I tried but failed, any suggestion or link or even code snippet would help a lot.

Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a ON a.Name = b. TableAName
INNER JOIN TableA a2 ON a2.Name = b. AnotherTableAName

Please help me with this, thanks in advance.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mycroft Holmes

Look into UNION. Split your query into 2 by removing the second join.

Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a2 ON a2.Name = b. AnotherTableAName
UNION
Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a ON a.Name = b. TableAName

Never underestimate the power of human stupidity RAH

KDGOLE

--Insert into #TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM #TableB b
INNER JOIN #TableA a ON a.Name = b.TableAName OR a.Name = b.AnotherTableAName

:) :) :)

KDGole

indian143

I did all of that, thanks for your help and what I felt was to use the Table variable and insert values into it, and then loop through table variable to check if that record combination doesn't exist in the table then insert the set. I like your approach too.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

indian143

I got it Kuldeep, thanks for your support, it is possible by using Merge and Table Variables.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."