How to insert a row into a linked server table?

sql-server insert linked-server
BanditoBunny picture BanditoBunny · Jun 22, 2012 · Viewed 55.5k times · Source

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 :)

Answer

Control Freak picture Control Freak · Jun 22, 2012 
INSERT INTO [TargetServer].[TestDatabase].[dbo].TestTable (Name)
SELECT Name From [SourceServer].[SourceDatabase].[dbo].[Names] where Id = 1

Related questions

error when insert into linked server

I want to insert some data on the local server into a remote server, and used the following sql: select * into linkservername.mydbname.dbo.test from localdbname.dbo.test But it throws the following error The object name 'linkservername.mydbname.…

Read More
Getting an Error on inserting data when using Linked Server Queries

UPDATE : The issue was col1 was hiereachyid type and even a select didnt work for it. Hi all, I am getting this error - Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through …

Read More
Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More