Select only few columns from procedure and insert into table

Question

I have a stored procedure that returns 6 columns. But I want to take only 2 columns and insert them into my table variable.

DECLARE @CategoryTable TABLE(   
CategoryId Int NOT NULL,   
Name nvarchar(255) NOT NULL   
)  

INSERT INTO @CategoryTable EXEC [GetAllTenantCategories] @TenantId  

When I run this

Column name or number of supplied values does not match table definition

How to insert only specified columns from a stored procedure?

I do not want to use SELECT INTO as it is not supported by SQL Azure

Tried below and got Invalid object name '#Temp'

DECLARE @CategoryTable TABLE(   
CategoryId Int NOT NULL,   
Name nvarchar(255) NOT NULL   
)  
INSERT INTO #Temp EXEC [GetAllTenantCategories] 1 

INSERT INTO @CategoryTable (CategoryId, Name) 
SELECT CategoryId, Name from #Temp

DROP TABLE #Temp

Solution 1

Refer the points taken from https://www.simple-talk.com/sql/performance/execution-plan-basics/

When the Estimated Plan is Invalid

In some instances, the estimated plan won't work at all. For example, try generating an estimated plan for this simple bit of code:

CREATE TABLE TempTable
(
Id INT IDENTITY (1 , 1 )
,Dsc NVARCHAR (50 )
);

INSERT INTO TempTable ( Dsc )
SELECT [Name]
FROM [Sales] .[Store] ;

SELECT *
FROM TempTable ;

DROP TABLE TempTable ;

You will get this error:

Invalid object name 'TempTable'.

The optimizer, which is what is used to generate Estimated Execution plans, doesn't execute T-SQL.

It does run the state­ments through the algebrizer , the process outlined earlier that is responsible for verifying the names of database objects. Since the query has not yet been executed, the temporary table does not yet exist. This is the cause of the error.

Running this same bit of code through the Actual execution plan will work perfectly fine.

Hope you got why your temp table approach not worked :) Because you might tried as T-SQL

Solution 2

You can create a temp table first and the INSERT the required columns in your table variable.

CREATE TABLE #temp
(
   your columns and datatype
)


INSERT INTO #temp
EXEC [GetAllTenantCategories] @TenantId

Then you can,

DECLARE @CategoryTable TABLE(   
 CategoryId Int NOT NULL,   
 Name nvarchar(255) NOT NULL   
 )  

INSERT INTO @CategoryTable (CategoryId, Name)
select CategoryId, Name from #temp

Also drop the #temp table,

DROP TABLE #temp

Solution 3

We can use OPENQUERY

SELECT EmployeeID,CurrentSalary INTO #tempEmp 
FROM OPENQUERY(LOCALSERVER,'Exec TestDB.dbo.spEmployee')

Comments

[bummi]

You will have to change either @CategoryTable or GetAllTenantCategories

[Billa]

@bummi, I changed @CategoryTable with all 6 columns defined. Still i am getting Column name or number of supplied values does not match table definition

[Billa]

@bummi, its my mistake, while altering @CategoryTable i added a column with IDENTITY(1,1) Specified :(

[Billa]

When i run it says Invalid object name '#CategoryTable'

[Praveen Nambiar]

you need to declare the table @CategoryTable like you have declared in your question....see my updates

[Billa]

DECLARE @CategoryTable TABLE( CategoryId Int NOT NULL, Name nvarchar(255) NOT NULL ) INSERT INTO #Temp EXEC [GetAllTenantCategories] @TenantId INSERT INTO @CategoryTable (CategoryId, Name) SELECT CategoryId, Name from #temp DROP TABLE #temp

[Billa]

I pasted my code again. It says Invalid object name #Temp. Do i need to declare #Temp?

[Praveen Nambiar]

silly silly mistake it seems....we inserted into #Temp...with a Upper Case T and now v r trying to access #temp...SMALLER CASE T

[Billa]

Its not a mistake at all. In SQL it is not case sensitive. Anyway, I tried changing case also. Still getting same error

[Praveen Nambiar]

see my updates...i wasn't aware that one needs to declare a temp table before using it with an SP... anyways it will work now..tried and tested...:)

[Billa]

Thanks. I got my temp table issue and switched to temp table approach