T-SQL Optimizing a Join on TOP Value from another table
You may want to consider partitioning instead of a scalar query.

So something like

insert into New_Table
    select
        [fst].Account_ID,
        [sst].Code
    from
        First_Source_Table as [fst]
            inner join (select
                            row_number()    over(
                                partition by Account_ID
                                order by Account_ID ) as [topN],
                            Account_ID,
                            Code
                        from
                            Second_Source_Table) as [sst]
            on     ( [sst].Account_ID = [fst].Account_ID )
    where
        ( [topN] = 1 ) --This is your topN query