SQL Server 2012 - Insert into linked server table using openquery

Question

I have a linked server Remoteserver containing a table that holds file and folder names from a dir

When I am on the remote server I can run a built in procedure (xp_dirtree) and populate the 'files' table but what i need to do is to run a query from the local SQL server that does this:

1. Delete all records from the [Files] table on Remoteserver

2. Insert data that comes from the stored procedure:

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

3. Select the 'subdirectory' column

I tried some things using openquery and i am able to select existing records but unable to do the insert.

Any help is appreciated.

Answer 1

Try this

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

OR

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');

But in general you do not need to use OPENQUERY at all if Fileserver and Remoteserver are accessible from the local machine.

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