Create a table or view from EXEC statement SQL Server

Question

EXEC('Select
          trim(field1) field1
          CVDATE(date1) date1
      from tabel1
      where trim(field1) <> 'ABC' ') at AE  --linked server

I need to dump the result generated from EXECUTION statement into a view. Is this possible? I am guessing the query is written this way is due to performance reasons

Answer 1

You can use the openquery to run the SELECT on remote server and create a view from the result set.

Here is a code sample. The sample creates local1 as the linked server and executes the query on it. If you already have your linked server registered, you don't need to run the step 1 and 4.

-- Step 1. register a linked server
exec sp_addlinkedserver 
        @server = 'local1',
        @srvproduct = '',
        @provider='SQLNCLI',
        @datasrc = 'NameOfTheRemoteServer' -- if server has multiple instances use 'NameOfTheServer\NameOfTheInstance'
go

-- Step 2. run the query on remote server and create a view out of the results
create view ViewTest
as
select * from openquery(
    local1, 
    'select 
        top 10 
        lower(AddressLine1) as Street,
        ModifiedDate as dt
    from AdventureWorks2014.Person.Address'
    )
go

-- Step 3. check the view
select * from ViewTest
go

-- Step 4. finally drop the linked server
sp_dropserver 'local1'
go