Insert data into table using select & execute procedure

Question

I want to insert data into table using both select statement and executing procedures.

That is... suppose a table 'TEMP' has 4 columns A,B,C,D. A & B column values will be coming from SELECT statement and C, D column values will be come by executing any stored procedures.

I have tried following but didn't facing error.

INSERT INTO TEMP
SELECT R1.A, R1.B, R2.C, R2.D FROM 
(SELECT A , B FROM SOME_TABLE) AS R1
CROSS JOIN EXEC [dbo].[SOME_PROCEDURE] @SOME_VARIABLE1 ) AS R2

ERROR IS:

Incorrect syntax near the keyword 'exec'

We have a similar question here. Try this : [How to combine stored procedure and select query result][1] [1]: stackoverflow.com/questions/11503067/… — Manish Kumar
– Manish Kumar, Commented Feb 4, 2014 at 6:34

pankeel · Accepted Answer · 2014-02-04 06:38:01Z

1

check this...

declare @var int

    exec EXEC [dbo].[SOME_PROCEDURE] @SOME_VARIABLE1,@output=@var


    INSERT INTO TEMP
    SELECT R1.A, R1.B, R2.C, R2.D FROM 
    (SELECT A , B FROM SOME_TABLE) AS R1
    CROSS JOIN @var) AS R2

answered Feb 4, 2014 at 6:38

pankeel

1,1331 gold badge10 silver badges22 bronze badges

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

1 Comment

Dhwani Over a year ago

YOU mean i should return output variable?

Mudassir Hasan · Accepted Answer · 2014-02-04 06:32:50Z

0

You cannot use Stored Procedure in JOIN . First store resultset of stored procedure in a temp table and then use this temp table in JOIN.

-- First create the table to which we want to push the SP result
CREATE TABLE #TempTableName(..........)

-- Insert result from the SP to temp table
INSERT INTO #TempTableName
EXEC [dbo].[SOME_PROCEDURE] @SOME_VARIABLE1

Then do the joining

INSERT INTO TEMP
SELECT R1.A, R1.B, R2.C, R2.D FROM 
(SELECT A , B FROM SOME_TABLE) AS R1
CROSS JOIN #TempTableName AS R2

answered Feb 4, 2014 at 6:32

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Collectives™ on Stack Overflow

Insert data into table using select & execute procedure

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related