Insert Variable based on an ID

Question

I want to use an INSERT query to insert multiple rows (100's) based on which ID's aren't in Table B but are in Table A. This i have managed to do, and most of the information that needs to be inserted i got sorted out except for one. This value is a value found in Table A that is different for each ID.

How can i insert correct corresponding Value from A to B based on where the ID's are the same? The Simplified tables:

TableA                            TableB
ID    |Value                      ID    | Value   | Other | Other
--------------                   ---------------------------------
1     | ABC                       1     | ABC     | ****  | ****
2     | DFG                       2     | DFG     | ****  | ****
3     | HBO                       3     | HBO     | ****  | ****

I tried using this query:

    INSERT INTO TableB
(
    ID,
    Value, 
    Other, 
    Other
)
SELECT 
    ID, 
    (SELECT TABLEA.Value FROM TABLEA,TABLEB WHERE TABLEA.ID = TABLEB.ID),
    ******, 
    ******

FROM TableA
WHERE ID IN 
(
    SELECT 
        TABLEA.ID
    FROM
        TABLEA

    MINUS

    SELECT 
        TABLEB.ID 
    FROM
        TABLEB
)

But obviously the problem with this is that it compares all ID's not the single one i need. Are there any solutions to do this inside the insert? Or do i need to insert first, leaving this value empty, and then use an UPDATE query to give it the correct value?

EDIT: Altough the problem mentioned is solved i stil have some other issues that are related. The following query is what is currently in the works:

MERGE INTO  TableB
USING   TableA
ON      (TableB.id = TableA.id) 
WHEN MATCHED
    THEN
        UPDATE
            SET TableB.VALUE = TableA.VALUE
WHEN NOT MATCHED
    THEN
        INSERT 
        (
            id, 
            POSITION,  
            other,    
            other, 
            other,   
            other, 
            other,
            VALUE
        )
        VALUES 
        (
            TableA.id,
            (
                SELECT POSITION
                FROM TableC
                WHERE something = '<String Value>'
                AND VALUE = 
                (
                    SELECT VALUE 
                    FROM TableA 
                    WHERE TableA.id = <The Current 'ID'> 
                ) , 
            )
            *****,    
            *****, 
            *****,    
            *****, 
            *****, 
            TableA.VALUE
        );

With the main question being, how can i get "Value" from the correct row so i am able to get the correct "POSITION".

Answer 1

IF I understand it correctly, you want to insert in TableB those rows from TableA that have ID's not present in TableB. For ID's present in TableB you want to update their values with values from TableA. Right?

If that is true, you want to use MERGE statement. Answer is similar to the one previous user posted, but corrected and this one should work fine:

MERGE INTO TABLEB tgt
USING TABLEA src
    ON (tgt.ID = src.ID) 
WHEN MATCHED
   THEN
       UPDATE
       SET tgt.value = src.value
WHEN NOT MATCHED
    THEN
        INSERT (
            ID
            ,VALUE
            )
        VALUES (
            src.id
           ,src.value
           );

Answer to the additional question:

MERGE INTO  TableB
USING   TableA
ON      (TableB.id = TableA.id) 
WHEN MATCHED
    THEN
        UPDATE
            SET TableB.VALUE = TableA.VALUE
WHEN NOT MATCHED
    THEN
        INSERT 
        (
            id, 
            POSITION,  
            other,    
            other, 
            other,   
            other, 
            other,
            VALUE
        )
        VALUES 
        (
            TableA.id,
            (
                SELECT POSITION
                FROM TableC
                    WHERE something = '<String Value>'
                AND VALUE = TableA.VALUE
            ) as position,
            *****,    
            *****, 
            *****,    
            *****, 
            *****, 
            TableA.VALUE
        );

Additionally, if you would want to merge tables only for ID's that are between 1200 and 1299 (as stated in the comments to my answer), try writing the code like this:

MERGE INTO  (SELECT * FROM TableB WHERE ID BETWEEN 1200 AND 1299) TableB 
USING   (SELECT * FROM TableA WHERE ID BETWEEN 1200 AND 1299) TableA 
ON      (TableB.id = TableA.id) 
WHEN MATCHED
    THEN
        UPDATE
            SET TableB.VALUE = TableA.VALUE
WHEN NOT MATCHED
    THEN
        INSERT 
        (
            id, 
            POSITION,  
            other,    
            other, 
            other,   
            other, 
            other,
            VALUE
        )
        VALUES 
        (
            TableA.id,
            (
                SELECT POSITION
                FROM TableC
                    WHERE something = '<String Value>'
                AND VALUE = TableA.VALUE
            ) as position,
            *****,    
            *****, 
            *****,    
            *****, 
            *****, 
            TableA.VALUE
        );