-1

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).

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. 

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

Versus 

INSERT INTO DW (<field1>,etc.) 
SELECT <field1>,etc. 
FROM [LINKED SERVER].<Schema>.dbo.<DBName>
WHERE ModifiedDate > @LastModifiedDate

Thank you.

Malennn
  • 1
  • 2

1 Answers1

0

You don't require CTE for your case as it is simple insert, you are not implementing any logic before insert. If you wanted to insert filtered or aggregated records, in that you may need first (CTE) approach.

Note: It has noting to do with linked server.

Vicky_Burnwal
  • 951
  • 9
  • 14
  • Thank you for your response. I edited my post to include that this should avoid blocking sessions in the production database. – Malennn Oct 20 '16 at 12:50