SQL Server how to insert into a table from linked server

Question

I have my local database prova and another database SOFIADR. I want to insert into the table Events of the prova database the rows of data retrieved from the linked database server SOFIADR. The database SOFIADR is a linked of my local database server test.

The following query in the database NKSOFIA works:

select *
from ARA_01.ARA01.dbo.monodosi_tab

If I move into my local database prova and try to import data in my table events I write:

insert into Events (.......)
    select field1, field2 .....
    from [SOFIADR].[NKSOFIA].ARA_01.ARA01.dbo.monodosi_tab

But I get an error:

Msg 117, Level 15, State 1, Line 3
The object name 'SOFIADR.NKSOFIA.ARA_01.ARA01.dbo.monodosi_tab' includes an excessive number of prefixes. Maximum capacity is 3.

How can I do that?

See http://stackoverflow.com/questions/4744878/error-when-insert-into-linked-server — user1327961, Feb 15 '17 at 18:26

score 1 · Answer 1 · answered Feb 15 '17 at 18:43

1

Use this syntax:

[LINKED SERVER].[DATABASE].[Schema].[TableName]

According to your pic:

[ARA2].[Database Name].[dbo].[monodosi_tab]

answered Feb 15 '17 at 18:43

McNets

10,352
3
32
61

score 0 · Answer 2 · answered Feb 07 '18 at 18:06

0

Due to the differences between ORA and MS-SQL use [LINKED SERVER].[USER_TABLE_SPACE (username)].[TableName] or [ARA_01]..[SOFIADR].[monodosi_tab]

answered Feb 07 '18 at 18:06

sinrtb

136
1
2
6

SQL Server how to insert into a table from linked server

2 Answers2