SQL Server Performance Forum – Threads Archive

SP To #table(selected columns)

Hi All, I have Temp table with two colums (ID,NAME) But I have SP that is returning 4 cloums (ID,NAME,AGE,SEX) i want to use " insert #temp exec sp_name " statement to insert all rows(selected columns)retun by Sp to Temp table Is it Possible? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Thanks & Regards Mathivanan K Great work may have to pass through these stages – ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

The syntax is correct, but the target table must have a column for each value returned by the SP. If there is a column for each value returned, in the same order, then this works fine: INSERT INTO #temp EXEC <sp_name> If your temp table contains more columns, or they are in a different order from the values returned by the SP, then you must add a column list after the table name: INSERT INTO #temp (<column_list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> EXEC <sp_name>

No, it is not possible. Your temp table structure must meet the resultset returned by the SP. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank, you’re right that there must be columns for all values, but the target table can have more columns. As long as you include a list of the target columns in the correct order … If the SP has OUTPUT variables, you could of course use code like this: declare @id INT, @name varchar(10) exec <sp_name> @id OUTPUT, @name OUTPUT insert #temp (ID, NAME) values (@id, @name)

Frank /Adriaan, Thanks lot for your immediate reply
In my case i am having 2 columns in #table but SP is returning 4 columns Many Thanks
Mathi
Thanks & Regards
Mathivanan K
Great work may have to pass through these stages – ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Frank, you’re right that there must be columns for all values, but the target table can have more columns. As long as you include a list of the target columns in the correct order … If the SP has OUTPUT variables, you could of course use code like this: <pre id="code">declare @id INT, @name varchar(10) exec <sp_name> @id OUTPUT, @name OUTPUT insert #temp (ID, NAME) values (@id, @name)</pre id="code"> <hr height="1" noshade id="quote"></blockquote id="quote"> Yes. you’re right. Thanks for the correction [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Mathi, You add two columns to your temp table, and just not use them. Or you create an SP that returns only the details you want. Are you sure you need to call an SP here? You can also use a plain old SELECT query as the source for an INSERT: INSERT INTO #temp
SELECT Id, Name
FROM <table_name>
WHERE <criteria> … although BOL does its best to hide this fact.

You can also try to make table udf from that stored procedure.

Adriaan, we are using link sever we have this sp in other database. for example only i gave (ID,NAME,AGE,SEX) like this,actualy we have lot of joins.In my project we are intracting othere databse using that database SP
Thanks & Regards
Mathivanan K
Great work may have to pass through these stages – ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

Mathi, Okay, understood. Just add dummy columns to your temp table – there’s no other way.

Thanks you guys Thanks & Regards
Mathivanan K
Great work may have to pass through these stages – ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

]]>