Search code examples
sql-serverinsertlinked-server

How to insert a row into a linked server table?

I have a server SourceServer I am connected to which has a linked server TargetServer.

How should an insert statement looks like (I need to reference Linked server, database, namespace, table):

//Connected to [SourceServer]

USE [SourceDatabase]

DECLARE @HelloWorld NVARCHAR(255)

SELECT @HelloWorld = Name From dbo.Names where Id = 1

INSERT INTO [TargetServer].[TestDatabase].dbo.TestTable (Name)   VALUES (@HelloWorld)

This statement executes with an exception:

Too many prefixes.

Update: The syntax as above works fine, the problem was expired password for the sql user used to connect to the linked server :)

Solution 
  • INSERT INTO [TargetServer].[TestDatabase].[dbo].TestTable (Name)
SELECT Name From [SourceServer].[SourceDatabase].[dbo].[Names] where Id = 1