SQL Server : save output from stored procedure output into a different table

Question

As a simple example, suppose I have the following stored procedure in my SQL Server database that produces an output:

CREATE PROCEDURE MyProc
    (@Id INT, 
     @Var1 VARCHAR(100))
AS
BEGIN
    SELECT
        Id = @Id,
        Var1 = @Var1,
        Var2 = LTRIM(@Id) + ' - ' + @Var1
END

Now, what I would like to do is have the output from this stored procedure inserted into a table with other columns such as:

DECLARE @NewTable TABLE 
(
     ExtraCols INT, 
     Id INT, 
     Var1 VARCHAR(100), 
     Var2 VARCHAR(MAX)
)

So, I would like to do something along the lines of:

DECLARE @RunVars TABLE (ExtraCols INT, Id INT, Var1 VARCHAR(100))
    INSERT INTO @RunVars (ExtraCols, Id, Var1)
    VALUES (123, 1, 'Test'),
           (456, 2, 'Test 1')

Then, after running each row of @RunVars through the stored procedure, to end up with:

@NewTable:

ExtraCols  Id       Var1        Var2
---------------------------------------------
123         1       'Test'      '1 - Test'
456         2       'Test 1'    '2 - Test 1'

I know I could create a temporary table to get the data from the stored procedure and then do a join myself, but I'm wondering if there's any better way to accomplish this - kind of a join or cross apply to the stored procedure.

Any special reason why you don't use SELECT...INTO... in your procedure? — Tyron78
– Tyron78, Commented Mar 19, 2018 at 17:06
In my initial procedure? That is because that is a procedure given to me by my DB Admins and I don't have control over its contents. As for using SELECT INTO in inserting the record, like I said, I know I could do it that way and join the results, but I'm curious to know if there's a way to do it via some kind of a join / cross-apply in SQL Server directly. — John Bustos
– John Bustos, Commented Mar 19, 2018 at 17:12
What about an opern query? That is, if you are allowed to enable data access — Tyron78
– Tyron78, Commented Mar 19, 2018 at 17:24
They severely frown upon it due to security concerns, but thanks, a good idea! — John Bustos
– John Bustos, Commented Mar 19, 2018 at 17:28
Can you write the proc as a function? Then you could leverage cross apply... — Eric Brandt
– Eric Brandt, Commented Mar 19, 2018 at 17:29

Tab Alleman · Accepted Answer · 2018-03-19 17:35:04Z

1

If you can't modify the proc, and OPENQUERY is frowned upon, then no, there's no better way.

answered Mar 19, 2018 at 17:35

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Bustos Over a year ago

Sucks, but I was hoping there was some way. Thanks.

Collectives™ on Stack Overflow

SQL Server : save output from stored procedure output into a different table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related