473,883 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Storing a stored-proc's result into a temp table

I'm trying to write a SQL that stores a result from a stored-procedure
into a temporary table.

Is there any way of doing this?
Jul 20 '05 #1
3 39507
"thirdman" <ta************ *@csfb.com> wrote in message
news:de******** *************** **@posting.goog le.com...
I'm trying to write a SQL that stores a result from a stored-procedure
into a temporary table.

Is there any way of doing this?

1) Create table test (
col001 varchar(10) null
)
go

2) insert into test
select substring(descr iption,1,10)
from someothertable
Jul 20 '05 #2

"thirdman" <ta************ *@csfb.com> wrote in message
news:de******** *************** **@posting.goog le.com...
I'm trying to write a SQL that stores a result from a stored-procedure
into a temporary table.

Is there any way of doing this?


Create a temp table with the same structure as the result set of the stored
procedure, then do this:

insert into #table
exec dbo.MyProc

But there are other options available also - in SQL 2000, a table-valued UDF
is often useful. See here for more information:

http://www.sommarskog.se/share_data.html

Simon
Jul 20 '05 #3
thirdman (ta************ *@csfb.com) writes:
I'm trying to write a SQL that stores a result from a stored-procedure
into a temporary table.

Is there any way of doing this?


Yes, you can say

INSERT #tmp (...)
EXEC your_sp

Beware that you cannot nest this constructs.

I don't know what you want to do, but this article mught include information
that is useful for you: http://www.sommarskog.se/share_data.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2312
by: Savas Ates | last post by:
i have a stored below it uses create temp table and drop temp table... when more than one user request the same page it probably returns error.. how can i solve this problem **************************************************************************** ***************************************** CREATE PROCEDURE st_seconddegree @fromwhom numeric(18), @towhom numeric(18) AS
0
1655
by: joe pribele | last post by:
I have this stored procedure that takes some xml as input. What I need to is use xml as a table so that I can join other tables to the xml and get information back that matches the criteria. I can use dxxshredxml to put it into a regular table and everything works great. but when I try to put it into a temp table it doesn't work. I get an error saying Session.ACCESSMASK doesn't have a column name ACCESS_MASK_ID. Do I have to use a...
0
2605
by: gwaddell | last post by:
I have a stored procedure that is loading data into a global temp table. Here is all the code for that stored procedure. CREATE PROCEDURE AS SET NOCOUNT ON BEGIN
17
4103
by: Jon Ole Hedne | last post by:
I have worked on this problem some hours now (read many-many...), and I can't solve it: In vba-code I create a table with Connection.Execute, and add some data to it. This table is saved in the backend-database. After generating this table, I want to open a report based on a query with data from this temporary table. In the report's OnOpen event I set the report's record source to the query pointing to the temporary table. If I run...
1
3731
by: Lauren Quantrell | last post by:
I have read the newsgroups and see this is a common issue but I saw no resolution for it: I have an Access2K frotn end and SQL Server 2K backend. In access, I create a temp table using code in a module: strSQL = "CREATE TABLE #tempTableName ("TempID int NOT NULL IDENTITIY (1,1) PRIMARY KEY, Field1 int...) CurrentProject.Connection.Execute strSQL,,adCmdText so far so good...
1
1777
by: BigD | last post by:
This all centers around an Access Data Project I have a stored procedure that aggregates events stored in a table based on intervals I specify. I have a form that supplies parameters for the stored procedure. My procedure creates a temp table to populate with the results. The problem I have is that I cannot create a form or report that uses the stored procedure as a source because the temp table is dropped when the stored procedure is...
2
15442
by: Lauren Quantrell | last post by:
I am using a stored procedure as the recordsource on an MS-Access2000 form: Forms!frmName.RecordSource = "dbo.myStoredProcedure" The stored procedure creates a temp table #Contacts and then inserts matching KeyID values contacts into it. Then I use a union query in the same stored procedure to create the actual recordset. What I want to know is how I can return the number of records in the temp table #Contacts back to MS Access and...
5
15050
by: das | last post by:
hello all, this might be simple: I populate a temp table based on a condition from another table: select @condition = condition from table1 where id=1 in my stored procedure I want to do this: if @condition = 0
16
10334
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
5
3154
by: Question123 | last post by:
Hello .. I want Output of SP1 stored procedure in #Temp TABLE OF SP2. Something like this ....... CREATE SP2 @iLoginId INT AS BEGIN
0
10728
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9558
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7959
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5782
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4200
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.