Insert Stored Procedure Result Into Temporary Table ?

Mar 21, 2006

I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.

Here is an example:


-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END

-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc

--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))

-- this call will succeed
INSERT INTO testTable EXEC testProc

View 5 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Use Stored Procedure Result To Insert Into Table

Mar 25, 2015

My stored procedure returns one row. I need to insert the result to a table. Can I right something like that:

=============
Insert into table1
exec myProc '1/1/15', 3/1/15'
=====

Or may be I can use Table-valued function insted of myProc?

View 4 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Temporary Table In Stored Procedure Doesn't Work From SQLDataSource

Feb 20, 2008

I have a stored procedure with the following:


CREATE TABLE #t1 (... ...);


WITH temp AS (....)

INSERT INTO #t1

SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...


This stored procedure works fine in Management Studio.

I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.

What does the error message mean and what have I done wrong?

Thanks.

View 5 Replies View Related

How To Use The Stored Procedure Result To Update The Table

Feb 11, 2008

Hi,

I am new to stored procedure.

My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.


Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK

I want to use the returned table name and update the set eh FK's value = null.

Some how it is not working , can some one point me out on correct solution.



CREATE PROCEDURE [dbo].[sp_delete_data]
AS
DECLARE @update_Var table (
REF_Table nvarchar(50),
FK_Column varchar(25),
PK_Table varchar(25),
PK_Column varchar(25),
Constraint_Name varchar(50));

insert into @update_Var
SELECT
FK.TABLE_NAME,
CU.COLUMN_NAME,
PK.TABLE_NAME,
PT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

WHERE PK.TABLE_NAME='usertable';

Select * from @update_Var



Thanks
Santosh Maskar

View 7 Replies View Related

EXEC Stored Procedure For Every Line Of SELECT Result Table - How?

Jul 23, 2005

Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert

View 2 Replies View Related

Problem In Executing Stored Procedure With Temp Table Returns Result Sets

Mar 23, 2006

Hi,
we are facing problem in executing a stored procedure from Java Session Bean,

coding is below.

pst = con.prepareStatement("EXEC testProcedure ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pst.setString(1, "IN");
//
rs = pst.executeQuery();
rs.last();
System.out.println(" Row Number "+rs.getRow());
rs.beforeFirst();
while(rs.next())
{
System.out.println(" Procedure is "+rs.getString(1));
}

same sp working perfectly with SQL Server 2000

am getting a error message

com.microsoft.sqlserver.jdbc.SQLServer
Exception: A server cursor cannot be opened on the given statement or statements
. Use a default result set or client cursor.



If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.

SP :

create proc testProcedure
@countrCode varchar(3)
as

select countryname INTO #TMPCOU from country where countryCode = @countrCode
SELECT COUNTRYNAME FROM #TMPCOU

Its really very urgent. Please help me...!

Rgds,

Venkatesh.

View 2 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

What Are Temporary Stored Procedure?

Jan 4, 2008

hi
what are temporary store procedure and where they are used,can u give me an example where the temporary stored procedures are used.
can we create  a procedur with in a procedure?
 

View 3 Replies View Related

VB5 Create Temporary Stored Procedure

Sep 3, 1998

When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.

My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?

The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240

View 4 Replies View Related

Using Temporary Tables Within The Stored Procedure

Nov 15, 2006

Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu

View 1 Replies View Related

Stored Procedure For Insert Data From One Table To Another Table

Apr 25, 2006

Hi,
     I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
 

View 1 Replies View Related

Is It Possible To Insert Data Into A Table From A Temporary Table That Is Inner Join?

Mar 10, 2008

Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789

View 2 Replies View Related

Querying Temporary Tables From A Stored Procedure

Jun 7, 2005

What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.

I am currently using SQL Server 2000 backend.  Each one of these queries are
pretty complex so I created each query as a Stored Procedure.  These queries are
dynamic by each user, so the results will never be the same globally.

What I have done so far was created a master stored procedure passing the
current user's username as a parameter.  Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory.  Each of the sub stored procedures all
have the same columns.  After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable.  I then bind the DataTable to a
Repeater.DataSource.

Problem: When the page is rendered, it returns nothing.  I
tested the master SP in the data environment and it works fine.

Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource?  Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?

Here is an example of the code from the master SP:


CREATE PROCEDURE dbo.TM_getAlerts      @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO

It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled.  I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly.  But the query in one of the
sub SP is a normal but complex select.

View 5 Replies View Related

Stored Procedure - Insert Many Rows From One Table To Another

Jun 24, 2005

Searched around for a sample, maybe I missed one?I have an insert statement....INSERT INTO objScores  (objID, studentId, courseId)  VALUES  (   @objID, @userId, @course)  This works fine, but problem is it reads only the first value from the tableDECLARE c_oId CURSOR FOR  SELECT objID  FROM objStructure  FOR READ ONLYThe userID and course will always be the same.But, the objID, there will be many in the table objStructure such as Lesson1, Lesson2..... Lesson19I need it to read all of them, 1 - 19 or whatever the count, could be 3 and insert all of them into the table.So, the insert needs to input Lesson1, userID, course ----- Lesson2, userId, course ----- Lesson3, userID, course ---- and so on.It must read the objID from the objStructure table and just tranfer all that is there that = the course.Links?  Suggestions?Thanks all,Zath

View 4 Replies View Related

Stored Procedure - Insert With Table Name From Parameter

Jul 31, 2006

First of all hi for all,

I m trying to make insert stored procedure with parameters, i want to send table name and insert values with parameters, i m trying that below stored procedure but it gives syntax eror to me what shoul i do to correct it ?



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[insertalepform]

-- Add the parameters for the stored procedure here

@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,

@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime

AS

BEGIN

SET NOCOUNT ON;

exec ('INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]

([TalepBirimNo], [BirimistekSayısı], [BirimistekTarihi], [Acıklama], [Onay]

,[Seviye], [TalepFormNo], [TalepTarih])

VALUES ('+@talepbrmno+','+@birimisteksayi+','+@birimistektarih+','+@aciklama+'

,'+@onay+','+@seviye+','+@talepformno+','+@taleptarih+') ')

END

View 12 Replies View Related

Can I Insert Records Into A Table From A Stored Procedure

Oct 9, 2007

I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset.

I use the logic in other sprocs too.

Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like....

INSERT INTO Insurance (Name)
Exec sp_AddNamesList

Thanks,

Mike

View 5 Replies View Related

Stored Procedure Issue - Problem With Temporary Tables

Jun 14, 2006

I would like to create a stored procedure which creates a temp table tostore some XML. The # of fields of XML is dependent upon the contentsof another table in the application, so the first part of my procedureidentifies the # of fields necessary. That is working correctly. Thesecond part of the procedure actually attempts to create the table.This is where my issue is.If I attempt to create the table as a non-temporary table, theprocedure executes correctly. As soon as I add the hash marks in frontof the table name to indicate that it is a temporary table, it isfailing. Is this a known bug? Or is my code just uncharacteristicallybad? ;)I'm getting an error that says "Invalid object name '#temp'."The section of code that has an issue is (the value of @max is 25 in mytest):SET @xq = 'CREATE TABLE #temp ( respid int, 'SET @i = 0WHILE( @i <= @max ) BEGINSET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + 'xml'IF ( @i < @max ) BEGINSET @xq = @xq + ', 'ENDSET @i = @i + 1ENDSET @xq = @xq + ' )'SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) )EXECUTE sp_executesql @nxq......DROP TABLE #temp

View 3 Replies View Related

How Can I Insert Id Field Automatic In Table By Stored Procedure

Sep 25, 2005

i have a  student table     and i created a  stored procedure   to insert a new student in this  table  but  student_id field  wich  i put it as  primary key got error  because  allready  record has same value  .how i can know  the last row's student_id value  and  input a new valid value    in one stored procedure thanks

View 1 Replies View Related

Select From Pivot Stored Procedure And Insert Into Table

Mar 10, 2008

hi need help
how to "select from pivot stored procedure and insert into table"
this line
INSERT INTO [nili].[dbo].[tb_pivot_edit]
tnx



Code Snippet
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)


DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080401', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN

SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 30
BEGIN

SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM (

SELECT ID,new_unit,mhlka_id,mhlka,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
INSERT INTO [nili].[dbo].[tb_pivot_edit]
([Fname]
,[new_unit]
,[mhlka_id]
,[mhlka]
,[fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5]
,[fld6]
,[fld7]
,[fld8]
,[fld9]
,[fld10]
,[fld11]
,[fld12]
,[fld13]
,[fld14]
,[fld15]
,[fld16]
,[fld17]
,[fld18]
,[fld19]
,[fld20]
,[fld21]
,[fld22]
,[fld23]
,[fld24]
,[fld25]
,[fld26]
,[fld27]
,[fld28]
,[fld29]
,[fld30]
,[fld31])

END

View 1 Replies View Related

Insert Record Into Temporary Table From A Select Statement

Jan 17, 2006

Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chua

View 4 Replies View Related

How To Find Query Plan For A Stored Procedure Using Temporary Tables

Oct 25, 2006

This post is related to SQL server 2000 and SQL Server 2005 alleditions.Many of my stored procedures create temporary tables in the code. Iwant to find a way to find the query plan for these procsRepro--***********************************use pubsgoCREATE PROCEDURE Test @percentage intASSET Nocount on--Create and load a temporary tableselect * into #Temp1 from titleauthor--Create second temporary tablecreate table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ordint, rolaylityper int)--load the second temporary table from the first oneinsert into #Temp2 select * from #Temp1goset showplan_Text ONgoEXEC Test @percentage = 100GOset showplan_Text OFFgo**************************************I get the following errorServer: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp2'.Server: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp1'.I do understand what the error message means. I just want to know abetter way of finding the query plan when using temp objects.My real production procs are hundreds of lines with many temp tablesused in join with other temp tables and/or real tables.Regards

View 3 Replies View Related

Can We Have Temporary Table In Store Procedure When Using BCP ?

Jul 21, 2006

hi, good day,

when i run query in query analyzer

exec sp_test '2006-07-21'


it show result , however when i run it using BCP tools


exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c: est.txt -c '


it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure

how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

thank you :eek:

View 14 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Temporary Table Insert Performance Degraded After Migration To 2005 By 50%.

Apr 11, 2007

Hi,



I have a series of queries which have doubled in the amount of time they take to execute since moving to SQL Server 2005. The queries being performance tested are utilising hardware that is very similar to that of the comparison SQL Server 2000 server. They have 6 CPUs exactly the same and we have swapped RAM around to eliminate that difference.



There are 4 parts to the query suffering performance degredation.



1. Create temporary results table

2. Create (using SELECT INTO) and populate temporary working table 1 - 212,263 rows

3. Create (using SELECT INTO) and populate temporary working table 2 - 5,102 rows

4. Insert into temp results table matches found in temp work table 1 and temp work table 2 - 382 rows



On 2000, the queries take a total of 15 secs. On 2005, they take 30 seconds. Part four of the query takes approx 17 secs on its initial run. However, if i truncate the temp results table and re-run just the last query it only takes 1 sec. Query Plan caching?



I have reviewed the forum for a solution to the problem but with no luck. Many of the solutions presented appear to relate to permanant user tables. Solutions such as UPDATE STATISTICS and recompiling stored procedures have no positive effect. When reviewing the query plans, there are very little differences. Some expected between versions right?



The following code snippet is the query from part 4.




Code Snippet

INSERT #MatchingResults

(Table1IDNo, Table2IDNo, MatchRunNo)

SELECT DISTINCT #Table2.IDNo AS Table2IDNo,

#Table1.IDNo AS Table1IDNo,

1 AS MatchRunNo

FROM #Table1

INNER JOIN #Table2

ON ( #Table2.LastName = #Table1.LastName )

AND ( #Table2.AddressDetails = #Table1.AddressDetails )

AND ( #Table2.Country = #Table1.Country )

AND ( ( #Table2.FirstName = #Table1.FirstName) OR ( #Table1.FirstName = '' ) )

AND ( ( #Table2.Title = #Table1.Title ) OR ( #Table1.Title = '' ) )



The query plan shows a hash join on both servers. I have tried removing the distinct statement and forcing a Loop Join (query hint).



I have also run SQL Profiler. The only differences there appear to be with the "SELECT StatMan" statements.



On 2000, part of the query duration is 1719 and is as follows:


Code SnippetSELECT statman([AddressDetails],[LastName],[FirstName],[Title],[Country],@PSTATMAN)
FROM (SELECT TOP 100 PERCENT [AddressDetails],[LastName],[FirstName],[Title],[Country] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________000000000096] WITH(READUNCOMMITTED,SAMPLE 3.675520e+001 PERCENT) ORDER BY [AddressDetails],[LastName],[FirstName],[Title],[Country]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)



On 2005, part of the query duration is 5188 and is as follows:


Code Snippet

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [AddressDetails] AS [SC0] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________00000000000E] WITH (READUNCOMMITTED,SAMPLE 7.946877e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)



Its clear that the sampling rate is higher. I assume this could have something to do with it. Can this be modified?



Thank-you for your help in advance..



Cheers



Tim















View 9 Replies View Related

SQL Server 2012 :: How To Insert One To Many Valued Parameters From A Stored Procedure Into A Table

Jan 14, 2015

I have a SP with Parameters as:

R_ID
P1
P2
P3
P4
P5

I need to insert into a table as below:

R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5

How can I get this?

View 2 Replies View Related

SQL Server 2014 :: Insert Stored Procedure Output To Table And Add Datetimestamp

Jun 22, 2015

I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.

insert into Table_Test execute sp_who

View 2 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

(re)using A Temporary Table In A Stored Proc (was Confusion)

Feb 15, 2005

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?


Howdy!

View 14 Replies View Related

Temporary Table In 3 Diffirent Stored Proc

Mar 30, 2004

I know there maybe something similar of what im asking for but i just cant find it.

I have 3 Stored procedure.

SPA - create a temporary table "sp_getListOfChildren"
SPB - insert the data into the temp table "sp_InsertCategoriesFound"
SPC - display the list of categories i found "sp_ListingAvailableCategories"

process:
SPA call SPB and SPC call SPA

my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..

Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"

============MY "SPC" CODE=============
alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier
AS

exec sp_getListOfChildren @CurrentCategoryID

select * from #TblTempCat

select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID

View 7 Replies View Related

Access Result Set From Storede Procedure W/in A Stored Procedure

Jan 24, 2004

Hi All

I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.

Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?

Thanks,

View 1 Replies View Related

Nested Queries, Stored Procedures, Temporary Table

Jul 23, 2005

Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved