Help with INSERT INTO..SELECT

I'm inserting a large number of rows into Table_A. Table_A includes a B_ID column which points to Table_B.B_ID.

Table B has just two columns: Table_B.B_ID (the primary key) and Table_B.Name.

I know the value for every Table_A field I'm inserting except B_ID. I only know th开发者_开发问答e corresponding Table_B.Name. So how can I insert multiple rows into Table_A?

Here's a pseudocode version of what I want to do:

REPLACE INTO Table_A (Table_A.A_ID, Table_A.Field, Table_A.B_ID) VALUES
(1, 'foo', [SELECT B_ID FROM Table_B WHERE Table_B.Name = 'A'),
(2, 'bar', [SELECT B_ID FROM Table_B WHERE Table_B.Name = 'B'),...etc

I've had to do things like this when deploying scripts to a production environment where Ids differed in environments. Otherwise it's probably easier to type out the ID's

REPLACE INTO table_a (table_a.a_id, table_a.field, table_a.b_id) 
SELECT 1, 'foo', b_id, FROM table_b WHERE name = 'A' 
UNION ALL SELECT 2, 'bar', b_id, FROM table_b WHERE name = 'B' 

If the values:

(1, 'foo', 'A'),
(2, 'bar', 'B'),

come from a (SELECT ...)

you can use this:

INSERT INTO Table_A
    ( A_ID, Fld, B_ID)

SELECT Data.A_ID
     , Data.Field
     , Table_B.B_ID
    FROM (SELECT ...) As Data
        JOIN Table_B
            ON Table_B.Name = Data.Name

If not, you can insert them into a temporary table and then use the above, replacing (SELECT ...) with TemporaryTable.

CREATE TABLE HelpTable
    ( A_ID int
    , Fld varchar(200)
    , Name varchar(200)
    ) ;

INSERT INTO HelpTable
VALUES
(1, 'foo', 'A'),
(2, 'bar', 'B'), etc...
;

INSERT INTO Table_A
    ( A_ID, Field, B_ID)
SELECT HelpTable.A_ID
     , HelpTable.Fld
     , Table_B.B_ID
    FROM HelpTable 
        JOIN Table_B
            ON Table_B.Name = HelpTable.Name
;

DROP TABLE HelpTable ;