SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 INSERT INTO only specific SP result fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 04/17/2013 :  08:14:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/17/2013 :  08:17:07  Show Profile  Reply with Quote
Whats the problem? I'm not getting......

--
Chandu
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 04/17/2013 :  08:26:07  Show Profile  Reply with Quote
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?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/17/2013 :  08:44:40  Show Profile  Reply with Quote
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

Edited by - bandi on 04/17/2013 08:49:10
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 04/17/2013 :  08:49:03  Show Profile  Reply with Quote
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.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 04/17/2013 :  09:06:55  Show Profile  Reply with Quote
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!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 04/17/2013 :  09:14:09  Show Profile  Reply with Quote
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".
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000