Query to Copy Table Data from local database to linked server database

Question

I am trying to copy all rows of a table from the database of my local machine to linked server database.

This is the query I am using:

DECLARE @Qry nvarchar(MAX)
DECLARE @Server nvarchar(50)
SET @Server = '[LINKEDSERVER]'
SET @Qry = '
      DECLARE @Qry2 nvarchar(max)
      SET @Qry2 = N''
      SET IDENTITY_INSERT RDB.dbo.Type ON
      insert into RDB.dbo.Type (id, Name) Select ID,Name From 
      [LOCALSERVER].localdb.dbo.Type
      SET IDENTITY_INSERT RDB.dbo.Type OFF''
      EXEC ' + @Server + '.RDB.dbo.sp_executesql @Qry2'
EXEC SP_EXECUTESQL @Qry

But I am getting this error - please help what to do here to make it work:

Could not find server 'LOCALSERVER' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

and If remove the [LOCALSERVER] then I get:

Invalid object name 'localdb'.

Please help if any other dynamic query can work to copy table data from local server to linked server.

Answer 1

You are trying to run the sql statement in remote linked server. In that remote linked server, there is no linkedserver(entry in sys.servers) defined for your local server. Hence, you are getting the error.

You can define linked server for your server in the remote machine and execute statement.

DECLARE @Qry nvarchar(MAX)
DECLARE @Server nvarchar(50)
SET @Server = '[LINKEDSERVER]'
SET @Qry = '
      DECLARE @Qry2 nvarchar(max)
      SET @Qry2 = N''
      SET IDENTITY_INSERT RDB.dbo.Type ON
      insert into RDB.dbo.Type (id, Name) Select ID,Name From 
      [LOCALSERVER].localdb.dbo.Type
      SET IDENTITY_INSERT RDB.dbo.Type OFF''
      EXEC ' + @Server + '.RDB.dbo.sp_executesql @Qry2'
EXEC SP_EXECUTESQL @Qry