OPENQUERY throwing "Out of memory" error: limit rows until all are loaded
<p>In SQL Server 2016 I have a stored procedure. In this SP I want to get all data from a view from a linked server (PostgreSQL) by using the following statement:</p>

<pre><code>INSERT INTO myTable
SELECT Field1,
    Field2,
    Field3,
    ...
FROM OPENQUERY(myServer, 'SELECT * FROM myDatabase.mySchema.myView')
</code></pre>

<p>When I use it like this, I'm getting the following error message after a few minutes:</p>

<blockquote>
  <p>Out of memory while reading tuples.</p>
</blockquote>

<p>I changed the <code>SELECT</code> statement in <code>OPENQUERY</code> to get only the first 1000000 rows which worked fine:</p>

<pre><code>SELECT * FROM myDatabase.mySchema.myView ORDER BY Field1 LIMIT 1000000
</code></pre>

<p>Now I am unsure what the most practical way to get all data would be. I could insert the first 1000000 rows and then insert the next 1000000 using <code>OFFSET</code>. But I don't think this would be a nice solution as I don't know what the total number of rows is. A loop would be another way but I really don't know if this would be the easiest way to achieve what I want.</p>

<p>Any help would be appreciated.</p>