I have the next T-SQL code:
DECLARE @A CHAR(1)
DECLARE @B CHAR(10)
DECLARE @C DECIMAL
DECLARE @D CHAR(1)
SET @A = 'S'
SET @B = '2015-03-23'
SET @C = 1
SET @D = 'P'
EXEC('CALL DATA_COLLECTOR(?,?,?,?)',@A, @B, @C, @D) at LINKED_SERVER_10
PRINT @D
@D is an input-ouput variable.
After running the code, the variable @D gets a result set (5 rows, 10 columns).
How can I copy the result set contained in @D to another table in my database?.
Thanks in advance.
To create a new table from the variable, use
SELECT * INTO newTable FROM @D
To put the data into an existing table, use
INSERT INTO existingTable SELECT * FROM @D
Your output cannot be CHAR(1)
If you ignore @D and just want to store the result set of your procedure into a table, then you have few choices to do:
Table has been created before
CREATE TABLE #Table (Field1 INT, Field2 VARCHAR(50));
INSERT INTO #Table
EXEC yourProcedure
To see the result
SELECT *
FROM #Table
Create table at runtime using OPENROWSET
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
SELECT * INTO #Table
FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.yourProcedure')
To see the result
SELECT *
FROM #Table
