CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2008-r2
Dhwani
Dhwani

Reputation: 7626

Insert data into table using select & execute procedure

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'

Upvotes: 0

Views: 3520

Answers (2)

pankeel
pankeel

Reputation: 1148

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

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

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

Upvotes: 0

Related Questions