Insert openquery results into an existing table

Question

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

Answer 1

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)