Using CTE vs direct query From Linked Server
<p>I am trying to refresh some tables in our data warehouse from the live production database and I am using Merge (for transactional tables that change data per record) and Insert (for historical/log tables).</p>

<p>I am curious to see what is the difference between using the structures below in terms of affecting the source database? I would need to be able to avoid blocking other sessions in the production database while this refresh is going on. </p>

<pre><code>WITH SRC AS (
     SELECT &lt;field1&gt;,&lt;field2&gt;,etc 
     FROM [LINKED SERVER].&lt;Schema&gt;.dbo.&lt;DBName&gt;
     WHERE ModifiedDate &gt; @LastModifiedDate
) 
INSERT INTO DW (&lt;field1&gt;,etc.) 
       SELECT &lt;field1&gt;,etc. 
       FROM SRC
</code></pre>

<p>Versus </p>

<pre><code>INSERT INTO DW (&lt;field1&gt;,etc.) 
SELECT &lt;field1&gt;,etc. 
FROM [LINKED SERVER].&lt;Schema&gt;.dbo.&lt;DBName&gt;
WHERE ModifiedDate &gt; @LastModifiedDate
</code></pre>

<p>Thank you.</p>