Sql-server – Insert openquery results into an existing table

dynamic-sqlexeclinked-serversql servert-sql

I have a stored procedure that queries a linked DB2 server in this fashion:

SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')'
exec sp_executesql @sql

I am trying to store that result into a new table via another stored procedure

    INSERT INTO [schema].[Table]
               ([column1]
               ,[column2])
    EXEC('schema.StoredProc')

Where StoredProc contains the aforementioned openquery

However, when I execute the second Stored Procedure (INSERT INTO), the output window prints out the text of @sql from my first stored procedure, and gives me the message:

SELECT * FROM openquery(DB2,'SELECT column1, column2 FROM table')
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Any help is appreciated

Best Answer

I will give you an example that has been working for me, it will give you an idea:

DECLARE @Radhe  TABLE(
    [n] [bigint] NOT NULL,
    [Login_Name] [nvarchar](128) NOT NULL,
    [Login_From] [nvarchar](128) NULL,
    [Account_Type] [nvarchar](128) NULL,
    THE_ORDER INT NOT NULL
    PRIMARY KEY CLUSTERED (Login_Name,N)
)



        SELECT @SQL = 'SELECT 
                        [n],
                        [Login_Name],
                        [Login_From],
                        [Account_Type],
                        the_order       
                      FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO @Radhe
        exec ( @sql)
Related Question