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.
"There is already enough hatred in the world lets spread love, compassion and affection."