How to insert a row into a linked server table? [closed]

Question

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

Sixpenny

Are you sure that your table or schema names doesn't has a '.' in it? If so, you will need to use [] again. – Conservatoire

there is no syntax error, please check the value of TargetServer value – Sining

The names a fully ok in my real query and fully fake here, I explicitly build a query so that the insert statement has less places which can cause an exception. – Sixpenny

Answer 1

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

Stortz

You don't need to rewrite a fake sql query. Does your answer mean that [TargetServer].[TestDatabase].[dbo].TestTable is acceptable? Why do I get a too many prefixes exception? – Sixpenny

Maybe this will help: social.msdn.microsoft.com/Forums/en/transactsql/thread/… – Stortz

Looking at the view statement the syntax at least should be ok, hmm. – Sixpenny

Answer 2

For those using openquery here's how to do it:

INSERT INTO OPENQUERY ([LINKEDSERVERNAME], 'SELECT idjob, salarylocal, salarydollars, calification FROM employee')
SELECT 1, 666, 668, 10

Fireplug

Answer 3

select * into [TargetServer].[TestDatabase].[dbo].TestTable
 From [SourceServer].[SourceDatabase].[dbo].[Names]

Queenie

the question was about insert – Tillich

This errors with The object name '[TargetServer].[TestDatabase].[dbo].TestTable' contains more than the maximum number of prefixes. The maximum is 2. Insert into rather than select * into does not error....but obvs will add to rows of existing table so truncate/delete as appropriate. – Mission

Answer 4

If the target table schema already there in Target use below code

INSERT INTO [TargetLinkedServerName].[TestDatabase].[dbo].[TargetTestTable] 
SELECT * From [SourceLinkedServerName].[SourceDatabase].[dbo].[SourceTestTable]

If the target table schema already not there in Target use below code(This code will create new table in Target as like source table)

select * into [TargetLinkedServerName].[TestDatabase].[dbo].[TargetTestTable] 
 From [SourceLinkedServerName].[SourceDatabase].[dbo].[SourceTestTable]

Acidforming

I know this a very long time since this post was made but I have never found an answer. The Select into returns this error. Msg 117, Level 15, State 1, Line 41 The object name 'TargetLinkedServerName.TestDatabase.dbo.TestTable' contains more than the maximum number of prefixes. The maximum is 2. – Giffie

Answer 5

If you want to insert data over a linked server the table needs to exist

--step 1 on TargetLinkedServerName: 

create table [TestDatabase].[dbo].[TargetTestTable]

--step 2 on SourceLinkedServerName:

INSERT INTO [TargetLinkedServerName].[TestDatabase].[dbo].[TargetTestTable] 
SELECT * From [SourceDatabase].[dbo].[SourceTestTable]

Discomfiture

Answer 6

I've got the same issue recently. I found the solution by using below method, which is storing the searched result into temporary table, then only insert that temporary table into the linked server table.

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

INSERT INTO [TargetServer].[TestDatabase].dbo.TestTable (Name)
SELECT * FROM #TempDBA

Autoclave