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.

Disprove

"unable" - what it mean? – Prom

can you include the error message you're getting? it looks to me like your table name is missing a part. To be a valid remote query it should have four parts: Remoteserver.dbname.dbo.files for example – Sidero

To be honest i am not quite sure about the right syntax to be used. I tried some variations of the openquery but i am sure thay are not right cause they didnt work :)...anyways the remote table name is as follows:[Remoteserver].[dbo].[files]....To clarify i dont need to pull the data into an existing table on the localserver...i Just need to display it on a web app so everything that needs to happen should happen remotely...the only thing that happens locally is the openquery that displays the data from the remoteserver 'files' table – Disprove

show us what you've tried so far? – Prom

INSERT OPENQUERY([Remoteserver], ' SELECT id,subdirectory,depth,isfile FROM [Remoteserver].[dbo].[files] ' ) VALUES ('EXEC master.sys.xp_dirtree ''\\fileserver\DBBackup'',1,1') – Disprove

and this one: SELECT * FROM OPENQUERY([Remoteserver], ' INSERT [Remoteserver].[dbo].[files] (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree ''\\Fileserver\DBBackup'',1,1; SELECT subdirectory FROM [Remoteserver].[dbo].[files] ' ) – Disprove

what is the name of the database on Remoteserver? – Prom

we can call it [RemoteDB]...in my comment i say [Remoteserver].[dbo].[files] but please read it as [RemoteDB].[dbo].[files] – Disprove

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;

Prom

Thanks Andrey! the first query worked...I have a question about the other 2 methods you mentioned...The localserver and remoteserver are in 2 different networks separated by a firewall and also the fileserver exists in the same network as the remoteserver so if i choose to use either the 2nd or the third method would it be the localserver that is executing the 'dirtree' command against the fileserver? – Disprove

not at all. if you are going to use OPENQUERY it might be any other server that is configured as linked server, but this may require the MSDTC to be enabled on each server (check this #27727067) – Prom

thank you for the link...I have a followup question related to the above so asking it here.... I have 3 commands in my stored procedure - first one deletes all records from a table , second inserts new records and third pull those records but at the moment when the query runs the delete works but i dont get the records from the 3rd statement which is the "select" ....I am unable to post the code here because it is too long for the comment block. – Disprove

it's bad. for now I can't help you without any information about the problem details. and for sure discussing your new problem here is definitely off-topic... you can try to figure it out or you could ask a new question... – Prom