Insert into table from procedure for single column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert into table from procedure for single column

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-09-17 : 09:33:21

Hi,

I have below code block and procedure returns multiple columns and my temp table have only 1 column.. Can I run multiple columns insertion?



ALTER PROC testproc
AS
BEGIN
	SELECT 1 AS ids, 2 AS nums
END
GO

-- Insert new records
CREATE TABLE #tmpOGroup (OrgGroupID INT)
INSERT #tmpOGroup (OrgGroupID) EXEC testproc

SELECT * FROM #tmpOGroup 
DROP TABLE #tmpOGroup

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 10:09:40

I haven't tried it the way you are doing it, but I suspect you cannot do this. The only workaround I can think of is to create an intermediate temporary table with two columns to insert into and then read the column that you want from that temp table.

Also, while I myself am guilty of using the INSERT..EXEC, the more read about it, the less I am a fan of that approach. For example, see these blogs by two people whose opinions are very well-respected and reliable:

http://www.sommarskog.se/share_data.html#INSERTEXEC
http://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:46:16

see

http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-09-18 : 02:30:22

Yes sunitabeck, thats true.. I will check those blogs..
and Thanks visakh for your link and input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:09:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources