insert into TargetTable
 (column1, column2, column3, column4, ... columnN)
 select
  'constant1', constant2, t1.some_column1, t2.some_column2, ... tN.some_columnN
 from
  (select some_column1, row_number() over (...) as rn from Table1 where ...) t1 join
  (select some_column2, row_number() over (...) as rn from Table2 where ...) t2 on t2.rn = t1.rn join
  ...
  (select some_columnN, row_number() over (...) as rn from Table1 where ...) tN on tN.rn = t1.rn;