title: Inserting large amount of rows from one table to another on different hosts
body: <p>I have two tables TABLE A and TABLE B. I'm trying to figure out the best way to do two things:</p>
  <ol>
  <li>Select a large number of rows from TABLE A (in the millions)</li>
  <li>And then make a query on TABLE B to check which rows don't exist there and then insert them.</li>
  </ol>
  <p>The two solutions i'm thinking of are:</p>
  <ol>
  <li>Store all the columns from table A in a temporary table and then join on table B to insert the rows but the two tables are on different connections
  OR</li>
  <li>Iterate through the rows i select from table A in batches and make multiple queries to table B</li>
  </ol>
  <p>Code for the two steps in Python using psycopg2 libary:</p>
  <pre><code>conn, cursor, engine = get_db_connection(&quot;table_a&quot;)
  query = &quot;&quot;&quot;
              SELECT *
              FROM table_a
          &quot;&quot;&quot;
  cursor.execute(query)
  # Problem here is i need to make a new connection to table_b as it's on a different host and figure out how to insert all the rows i queried before
  conn, cursor, engine = get_db_connection(&quot;table_b&quot;)
  query = &quot;&quot;&quot;
              INSERT INTO
              table_b
              VALUES
          &quot;&quot;&quot;
  cursor.execute(query)
  </code></pre>
  <p>Also codewise i'm not sure how i'm supposed to create a temporary table and reuse it in a new connection in Python.</p>