Search code examples
sql-serversql-insertdatabase-management

Creating a join table in SQL Server

Let's say I have two tables, A and B, each with unique ID columns A_id and B_id respectively. Then let's say I wake up one day and decide that the two tables have a relationship. So, I create a table AB that contains A_id, B_id pairs. Then I go to write a SQL server script that inserts these pairs based on other data in the tables, say A_name and B_name. I'd expect the actual insertion to work something like this (though with more advanced WHERE clauses typed in by the user though a Powershell script or something):

INSERT INTO AB (A_id, B_id) 
VALUES 
((SELECT (A_id) FROM A WHERE A_name = 'bob'), 
 (SELECT (B_id) FROM B WHERE B_name = 'john'))

I'm not sure of the correct syntax for such an operation. Can anyone point me in the right direction?

Solution

  • Instead of selecting from two sub-selects, you should select from a join of the two tables, using whatever logic you are going to use:

    INSERT INTO AB (A_id, B_id)
SELECT a.A_id, b.B_id
FROM A 
INNER JOIN B
 ON A.SomeColumn=B.SomeColumn

    Or, to replicate your example more precisely, it would look like this:

    INSERT INTO AB (A_id, B_id)
SELECT a.A_id, b.B_id
FROM A 
INNER JOIN B
 ON A.A_name='bob'
 AND B.B_name='john'