How to insert a row into a linked server table?

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

Are you sure that your table or schema names doesn't has a '.' in it? If so, you will need to use [] again. — StuartLC, Jun 22 '12 at 08:57
there is no syntax error, please check the value of TargetServer value — Romil Kumar Jain, Jun 22 '12 at 09:04
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. — BanditoBunny, Jun 22 '12 at 09:05

score 22 · Accepted Answer · answered Jun 22 '12 at 09:00

22

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

answered Jun 22 '12 at 09:00

Control Freak

12,965
30
94
145

1

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? – BanditoBunny Jun 22 '12 at 09:04
Maybe this will help: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/5281a959-3a20-4466-af63-272cac97166a – Control Freak Jun 22 '12 at 09:07
Looking at the view statement the syntax at least should be ok, hmm. – BanditoBunny Jun 22 '12 at 09:14

score 2 · Answer 2 · answered Jun 18 '22 at 20:05

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

answered Jun 18 '22 at 20:05

Valeria Chinchilla

21
1

score 1 · Answer 3 · answered Feb 13 '20 at 08:28

1

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]

answered Feb 13 '20 at 08:28

Jeyavel

2,974
10
38
48

1

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. – TheL0grus Jun 02 '21 at 18:42

score 0 · Answer 4 · edited Aug 03 '16 at 20:21

0

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

edited Aug 03 '16 at 20:21

Abdulla Nilam

36,589
17
64
85

answered Aug 03 '16 at 18:26

jai

11

the question was about insert – Ozzy Dec 04 '18 at 12:12
1

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. – Hilary Nov 11 '21 at 08:33

Theo Ekelmans · Answer 5 · 2022-11-10T16:28:33.010

0

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]

edited Nov 10 '22 at 16:28

answered Nov 10 '22 at 16:27

Theo Ekelmans

1
2

How to insert a row into a linked server table?

5 Answers5

Linked