Insert data from local to [LINKEDSERVER]

jjhzyzn0 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(106)

I need to insert data from my own local table to a table in my built linkedserver. I'm having some trouble. Please see.

SELECT TOP 100 * FROM [LinkedServerName].[database].[Schema].[table]

The data can be queried, and the linkserver connection is no problem

INSERT INTO [LinkedServerName].[database].[Schema].[table]  
(row1, row2) VALUES (value1, value2)

error message: Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.

EXEC ('INSERT INTO [database].[Schema].[table] (row1, row2) VALUES (value1, value2)') AT [LinkedServerName]

This insert works, it succeeds, but I have a lot of data, inserting one by one with a cursor will affect the speed.

EXEC ('INSERT INTO [database].[Schema].[table] (row1, row2) SELECT r1,r2 form [mylocalserver].[database].[Schema].[table]') AT [LinkedServerName]

[mylocalserver].[database].[Schema].[table] doesn't exist LinkedServer.

So how can I reference my LOCAL table inside the "EXEC() AT" syntax?

I also tried insert openquery

insert into openquery([LinkedServerName],'Select row1, row2 from [database].[Schema].[table]' )
select r1, r2 from [mylocalserver].[database].[Schema].[table]

error message: Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.

I want to know how to solve it, thank you all.

sql-server

来源：https://stackoverflow.com/questions/75551963/insert-data-from-local-to-linkedserver

1条答案

按热度按时间

vtwuwzda1#

Probably you can try like this:

CREATE TABLE #temp_table (
    row1 datatype,
    row2 datatype
)

INSERT INTO #temp_table
(row1, row2)
SELECT r1, r2
FROM [mylocalserver].[database].[Schema].[table]

INSERT INTO [LinkedServerName].[database].[Schema].[table]
(row1, row2)
SELECT row1, row2
FROM #temp_table

DROP TABLE #temp_table

赞(0）回复(0）举报 2023-02-28

我来回答

Insert data from local to [LINKEDSERVER]

1条答案

相关问题

热门标签

最新问答