Create a temp table, loop, add data, select from it? "Query has no destination for result data"
When I try to get the data back from the table I get an error.

That's because you cannot return anything from a DO statement. You would need a function where you define the return type in the RETURNS clause. (But you still wouldn't need a temporary table.)
Plus, PL/pgSQL does not allow SELECT without a target.
But you don't need any of this for the example. A plain query does it:
SELECT a.accountid::int, p.id::varchar AS product_uid, a.sampleproductId::text
FROM   product.product p
JOIN   product.accountproductmap a ON a.productId = p.id::int
WHERE  p.uid = ANY ('{abc1, abc2}'::text[]);

Some of the casts are probably unnecessary.
You might wrap it into an SQL function (PL/pgSQL not needed for this). See:

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?
Difference between language sql and language plpgsql in PostgreSQL functions

Aside: use legal, lower-case identifiers! See:

Are PostgreSQL column names case-sensitive?