SQL Server 2012 - Insert related servers into a table using openquery

Question

I have a linked server Remoteservercontaining a table containing the names of files and folders from a directory

When I'm on a remote server, I can run the built-in procedure ( xp_dirtree) and populate the “files” table, but I need to execute a query from a local SQL server that does this:

• Delete all records from the table [Files]onRemoteserver

• Paste the data that comes from the stored procedure:

  INSERT [Remoteserver].[dbo].[files] (subdirectory,depth,isfile)
     EXEC master.sys.xp_dirtree '\\Fileserver\DBBackup',1,1;
  

• Select the subdirectory column

I tried some things using openquery and I can select existing records, but could not perform the insert.

Any help is appreciated.

Answer 1

INSERT INTO OPENQUERY([Remoteserver]
    ,'SELECT subdirectory, depth, [file] FROM [RemoteDB].[dbo].[files]')
EXEC master.sys.xp_dirtree '\\fileserver\DBBackup', 1, 1;

INSERT INTO OPENQUERY([Remoteserver]
    ,'SELECT subdirectory,depth, [file] FROM [RemoteDB].[dbo].[files]')
select * from OPENQUERY([another_server_name], 'master.sys.xp_dirtree ''\\fileserver\DBBackup\temp'', 1, 1');

OPENQUERY, Fileserver Remoteserver .

INSERT INTO [Remoteserver].[RemoteDB].[dbo].[files] (subdirectory, depth, isfile)
   EXEC master.sys.xp_dirtree '\\Fileserver\DBBackup',1,1;