Insert results of Stored Procedure into Temporary Table in SQL Server

Last Reply 6 months ago By chetan

Posted 6 months ago

I wanted to insert the result of stored procedure into the temporary table.how to achieve it ?

The below is my Stored procedure.I am new to Sp.

ALTER procedure [dbo].[insertpropertylisting]
@propertyname varchar(150),
@propertyaddress varchar(250),
@propertyprice money,
@availableunits varchar(100),
@propertyid int OUTPUT
as 
BEGIN
        insert into propertylisting(propertyname,propertyaddress,propertyprice,availableunits)
        values(@propertyname,@propertyaddress,@propertyprice,@availableunits)
        print @propertyname
        select @propertyaddress as 'address'
        SET @propertyid=SCOPE_IDENTITY()

        SELECT @propertyname=propertyname,@propertyaddress=propertyaddress,@propertyprice=propertyprice,
        @availableunits=availableunits 
        FROM propertylisting 
        WHERE property_id=@propertyid
       Return @propertyid
END

 

Posted 6 months ago

Hey chetan,

Please refer below sample query.

SQL

CREATE PROCEDURE TempDetailsInsert
	@Name VARCHAR(150),
	@Country VARCHAR(250)
AS
BEGIN
       INSERT INTO Customers(Name,Country)
       VALUES(@Name,@Country)
       SELECT Name, Country FROM Customers
END
GO
CREATE TABLE #temp
(
	Name VARCHAR(150),
	Country VARCHAR(250)
)
INSERT INTO #temp
EXEC TempDetailsInsert 'Test','Test'
SELECT * FROM #temp
DROP TABLE #temp

 


Posted 6 months ago

This worked for me.

ALTER procedure [dbo].[insertpropertylisting]
@propertyname varchar(150),
@propertyaddress varchar(250),
@propertyprice money,
@availableunits varchar(100),
@propertyid int OUTPUT
as 
BEGIN
  insert into propertylisting (
        propertyname,
        propertyaddress,
        propertyprice,
        availableunits)
    OUTPUT
        inserted.propertyname,
        inserted.propertyaddress,
        inserted.propertyprice,
        inserted.availableunits,
        inserted.property_id
    values(
        @propertyname,
        @propertyaddress,
        @propertyprice,
        @availableunits)
END
Create table #propertylisting
(
    propertyname varchar(150),
    propertyaddress varchar(250),
    propertyprice money,
    availableunits varchar(100),
    propertyid int 
)
Insert into #propertylisting (
    propertyname,
    propertyaddress,
    propertyprice,
    availableunits,
    propertyid)
Exec [dbo].[insertpropertylisting] 
Drop table #propertylisting