SQL Server链接服务器INSERT INTO

I have problem with linked server at store procedure, I want to get data from linked server then insert into table.

我在存储过程中遇到链接服务器的问题，我想从链接服务器获取数据然后插入表。

This is my stored procedure:

这是我的存储过程：

CREATE PROCEDURE [dbo].[SP_GETPRODRECORD]
    @PR_NO varchar(10)=''
AS 
BEGIN
    DECLARE @OPENQUERY nvarchar(4000), 
            @TSQL nvarchar(4000), 
            @LinkedServer nvarchar(4000)  

    CREATE TABLE AAA (PR varchar(10))

    SET @LinkedServer = 'LS'

    SET @OPENQUERY = 'INSERT INTO AAA SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
    SET @TSQL = 'SELECT PSHN9G FROM F9G00 WHERE PSHN9G='''''+@PR_NO+''''')'

    EXEC (@OPENQUERY+@TSQL)
END

My problem is that the EXEC is not running, when I try to insert manually with code below is working

我的问题是EXEC没有运行，当我尝试手动插入下面的代码工作

INSERT INTO AAA(PR) 
    SELECT PSHN9G 
    FROM OPENQUERY(WAVEDLIB,'SELECT PSHN9G FROM F9G00 WHERE PSHN9G=''XXXXXXX'')

Am I missing something?

我错过了什么吗？

Thanks

谢谢

1 个解决方案

#1

There is no need to use OPENQUERY and EXECUTE, you can simply reference LinkedServer if you know which DB is your table into:

没有必要使用OPENQUERY和EXECUTE，如果你知道哪个数据库是你的表，你可以简单地引用LinkedServer：

CREATE PROCEDURE [dbo].[SP_GETPRODRECORD]
    @PR_NO varchar(10)=''
AS 
BEGIN
--No need to CREATE TABLE every time you exec SP. SELECT INTO #temp table instead.
SELECT PSHN9G INTO #temp FROM WAVEDLIB.DBName.dbo.F9G00 WHERE PSHN9G=@PR_NO
SELECT * FROM #temp
END