INSERT INTO only specific SP result fields

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 only specific SP result fields

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-04-17 : 08:14:05

I know I can create a temporary table using a SP like this:

CREATE TABLE #mytemptable (A int null, B int null, C int null)
INSERT INTO #mytemptable
EXEC mystoredprocedure @oneparameter, @anotherparameter;

Can I avoid creating a full table (and possibly an insanely long) CREATE TABLE somehow by taking only the field A? T-SQL doesn't seem to like back-to-back FROM and EXEC which creates this problem for me.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-17 : 08:17:07

Whats the problem? I'm not getting......

--
Chandu

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-04-17 : 08:26:07

If I cut out from CREATE TABLE this part ", B int null, C int null" I get as a result:
Msg 213, Level 16, State 7, Line 2
Column name or number of supplied values does not match table definition.

which is pretty clear why, but how do I get rid of that? Logically I think I need to only select column A, but how does the syntax there then go?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-17 : 08:44:40

For this type of insertion, you have to use OPENQUERY()
Follow this link http://cavemansblog.wordpress.com/2012/04/14/sql-server-how-to-select-data-from-a-stored-procedure/

Check Retrieving Data from External Tables for another type:
https://www.simple-talk.com/sql/learn-sql-server/working-with-the-insert-statement-in-sql-server/

--
Chandu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 08:49:03

The record set returned from the stored procedure has to match the columns in the insert statement. So you have to have at least as many columns in the table as are returned by the stored proc.

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-04-17 : 09:06:55

If I go with that OPENQUERY, is there any estimations about how slow it might make my code? I mean, does that slow down the code considerably when SQL code is OPENQUERY-connected into the same server where the code is running anyway?

But good stuff to read, thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 09:14:09

A good catalog of possible approaches is described in Sommarskog's blog here: http://www.sommarskog.se/share_data.html He lists the openquery as being one that is "Tricky with many pitfalls. Discouraged".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources