#temp Query

Dec 2, 2005

I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.

I've tried something like the following with no success:

Select * from tempdb..#tempTable

View 6 Replies


ADVERTISEMENT

Help With A SQL Query Using Temp Tables

Aug 30, 2004

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.

View 3 Replies View Related

Query Analyzer --Temp Tables

Nov 16, 2004

Is there a quick way to create a temp table that is the same as an existing table in the schema? I am used to Informix where "select * from <table A> into temp B" would create the temp table B with the same schema is table A. Is this same functionality available with MS SQL server using query analyzer?

View 2 Replies View Related

How To Use More Than One Query To Fill Temp Table

Jan 21, 2013

I have three queries that I use to determine what the party type of a person.

They will be either a child, family member or an associated party and I need to go to three different tables to find this out. I set a field "relationship" to child, family member or associated party depending on the query results.

I would like to fill a temp table with the results so I can use it in reporting.

Here is my code:

Code:
SELECTvap.partyID, relationship
INTO#Relationship
SELECTvap.partyID, Relationship = 'Child'
FROMVolunteerActivityParty vap JOIN
VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN

[code]....

View 5 Replies View Related

One Query Instead Of Using Multiple Temp Tables?

Aug 29, 2013

I have the following query and I would like to combine it into one query instead of using several temp tables.

IF OBJECT_ID('Tempdb..#a') IS NOT NULL DROP TABLE #a
SELECT *
INTO #a
FROM #Web_table w WITH(NOLOCK)
WHERE w.generated_date IS NOT NULL
IF OBJECT_ID('Tempdb..#b') IS NOT NULL DROP TABLE #b

[code]....

View 2 Replies View Related

How To Avoid Temp Tables And Do It In One Query?

Feb 5, 2008

I have a table T1 with columns: Date, Salesman, Sales. Salesman is a userid for each person and Sales is a decimal capturing sales done on Date.

From the data in T1 I would like to have a table with the following:

Salesman Sales-To-Date Sales-Last-Week

I have been able to do it with a temporary table, temp, as follows:


select Salesman, Sum(Sales) as ToDate into temp from T1 group by Salesman


with T2 as (

select Salesman, Lastweek=sum(Sales) from T1 where week>='1/28/2008' and week<='2/3/2008' group by Salesman

) select temp.Salesman, ToDate, Lastweek from temp full join T2 on temp.Salesman=T2.Salesman



How can I do the above in one query statement?

Thanks

View 4 Replies View Related

Easy Q.. Query Without Temp Table

Aug 7, 2007

How do I rewrite this query so that I do not use a temp table?


SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

View 3 Replies View Related

Temp Table For Repeatedly Used Sub Query

Nov 3, 2007

Hi,

Here is my query which lists all orders for products supplied by Supplier-3.
A typical Query on the Northwind database i wrote is like this..

Select * FROM [Order Details] WHERE ProductID in
(Select ProductID From Products where SupplierID = 3)

The subquery in Red was used in multiple places in one of my Stored Procedures..

So what i thought was - use a temp table to store the resultset from this subquery, and then use the temp table instead of querying the Products table everywhere..

My Query looked something like this..

Declare @ProductIDs TABLE
(ProductID int)

INSERT INTO @ProductIDs
Select ProductID From Products where SupplierID = 3

Select * FROM [Order Details] WHERE ProductID in
(Select ProductID FROM @ProductIDs)



Well, I expected an increase in performance with the latter approach, but seems my Stored Procedure is taking more time with the second solution..

Would be glad to see ne explanation on this behavior..

Thanks in Advance..

View 2 Replies View Related

Query Output As Text Rather Than A Temp Table..

Dec 3, 2006

Hi Guys,

I'm trying to figure out how to output a query as text instead of a tempory table...

I thought perhaps i could use this:

SELECT PRINT CustomerID FROM ORDER_TABLE

But that doesn't work

PRINT CustomerID FROM ORDER_TABLE

Doesn't work either...


I need this so i can print a customised invoice in SQL. That is, unless their is a better way of tackling this problem?

thx for reading :)

--Philkills

View 5 Replies View Related

Inserting Results From Query Into New Temp Table

Sep 17, 2013

I am try to insert the results from the query into new temp table but keep geeting an error for Incorrect syntax near ')'.

select * into tempCosting
from
(
select top 10* from itemCode itm
where itm.type= 1
)

View 3 Replies View Related

Insert Into Temp Results Of A Union Query

Nov 20, 2007

Hi,
I have follwing union query. I want to put this all in a temp table.

select Store_Id,batchnumber
From
Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641)
group by Store_Id, batchnumber
Union
select DestinationId,b.batchNumber
from
batch b
inner join Carton C on C.Carton_Id = b.General_ID
inner join Document d on d.Document_Id = c.Document_Id
where b.BatchType = 'Warehouse' and b.TranTable = 'Carton'
and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007'
Union
select d.DestinationId,b.Batchnumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferIn' and b.TranTable = 'Document'
and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
Union
select d.SourceId,b.batchNumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferOut' and b.TranTable = 'Document'
and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
order by batchnumber

Kindly advice.

Thanks
Renu

View 2 Replies View Related

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?

Nov 17, 2004

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?

View 2 Replies View Related

Unable To Extend Temp Segment By 64 In Tablespace TEMP (SSIS Error While Copying Data From Oracle)

Oct 22, 2007

I am transferring data from oracle and getting below error message.

I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion

[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".

View 4 Replies View Related

Query Based On Two Temp Tables Runs Forever

Oct 28, 1998

View 1 Replies View Related

Multiple Db Query Call From Within Different Context Into #temp Table

Mar 21, 2007

The first query returns me the results from multiple databases, thesecond does the same thing except it puts the result into a #temptable? Could someone please show me an example of this using the firstquery? The first query uses the @exec_context and I am having achallenge trying to figure out how to make the call from within adifferent context and still insert into a #temp table.DECLARE @exec_context varchar(30)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT DBNAMEFROM DBINFOWHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')ORDER BY DBNAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @exec_context = @DBNAME + '.dbo.sp_executesql 'set @sql = N'select top 10 * from products'exec @exec_context @sqlFETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursor-------------------------------------------------------------------------------------CREATE TABLE #Test (field list here)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT NAMEFROM sysdatabasesWHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULLORDER BY NAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'INSERT INTO #Testexec (@sql)FETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursorSELECT * from #TestDROP TABLE #Test

View 1 Replies View Related

SQL Server 2008 :: Turning Complex Query Into Temp Tables

Mar 5, 2015

do you have a general rule of thumb for breaking a complex query into temp tables? For someone who is not a sql specialist, a query with more than a few table joins can be complex. So a query with 10+ table joins can be overwhelming for someone who is not a sql specialist.

One strategy is to break a problem into pieces so to speak by grouping together closely related tables into temp tables and then joining those temp tables together. This simplifies complex SQL and although not as performant as one big query it's much easier to understand. So do you have a general rule of thumb as far as a threshold for the number of joins you include in a query before you break the query into temp tables?

View 9 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Transact SQL :: Confirmation Of UNION ALL Query For INSERT INTO Temp Table

Jul 21, 2015

I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.

In my first SELECT statement, I have INSERT INTO @tblData.

Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?

DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table

[Code] ....

View 3 Replies View Related

SQL Server 2008 :: Storing Dynamic Query Output In Temp Table

Apr 6, 2015

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.

Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

View 3 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!

The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.

Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index. 

And also I am not sure, whether creating index in more columns will create issue or not.

Attached the update query and index query.

CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC, 

[Code] ....

View 7 Replies View Related

Temp Tables Vs Temp Variables

Jul 20, 2005

I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet

View 2 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

Query Oracle Table In SQL &&amp; Put In Temp Table

Nov 6, 2006

Hi all,

I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?

This is the code i'm using for the query:

DECLARE @cmd VARCHAR(500)
declare @Year varchar(25)
set @Year = '2006'

DECLARE @Link VARCHAR(100)
DECLARE @Table VARCHAR(100)

select @Link = Server from tbl_Conn where Area='Floor'
select @Table = Target_Table from tbl_Conn where Area='Floor'

SET @cmd =
'
select * from OPENQUERY
(
' + @Link + ',
''
UPDATE '+ @Table +'
SET TARGET_VALUE = '+@Value+'
WHERE Date = '+@Year'
''
)
'
EXEC (@cmd)

How do I put the executed results into a TEMP table ?

Rgds,

View 2 Replies View Related

Temp Table Vs Global Temp Table

Jun 24, 1999

I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

View 2 Replies View Related

Temp DB

Jan 26, 2001

What conuter can you check on NT to see what TEMP DB is doing.
Also, How can you check how hard Temp DB is working.

Thank you,
John

View 1 Replies View Related

Temp Db

Apr 2, 2008

Hi all ,
this is chetan .SQl server DBa.
i want to know that what should be done when tempdb is full.

Thanks in advance...

Thanks in advance.
-- Chetan

View 4 Replies View Related

Temp Db

Jul 20, 2005

what is the best practice for sizing the temp db in sql 2000?what the best way to free up temp db space without shutdown the sql?how to monitor temp db parameters?

View 1 Replies View Related

TEMP TABLE

May 31, 2008

HI ALL,iam creating a temporary table using following Stored procedure but when i complile the Stored procedure iam getting the following errorServer: Msg 2714, Level 16, State 1, Procedure SP!, Line 15There is already an object named '#TEMP2' in the database.the stored procedure is  ALTER PROC SP1@SELECT VARCHAR(15)=NULL  AS  BEGIN  IF @SELECT ='FOLDER'BEGIN     SELECT            DISTINCT(HIERARCHY_ID),HIERARCHY_NAME,HIERARCHY_DESCRIPTION,HIERARCHY_PARENT_ID                      INTO #TEMP2      FROM                 BM_HIERARCHY_MASTER        ENDELSE IF @SELECT='PAGE' BEGIN        SELECT            DISTINCT(HIERARCHY_ID),HIERARCHY_NAME,HIERARCHY_DESCRIPTION,HIERARCHY_PARENT_ID                            INTO #TEMP2      FROM                 BM_HIERARCHY_MASTEREND  

View 5 Replies View Related

UDF Vs Temp Table

Apr 4, 2006

Which one is better to use User Defined function or Temp Table?

I am working on SQL Server 2005, and I have a user defined function that returns a table. I need to run queries on this table.

I am not sure if I should run the function once and store the results in a temp table and run queries on the temp table or call the UDF multiple times. I am concerned about performance.

Thanks

View 3 Replies View Related

DTS - SP And Temp Tables

Nov 3, 2000

I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid Object name "#tempT1"'

Is there a way to make a DTS package call a stored procedure that uses temp tables?

Thanks.

View 2 Replies View Related

Temp Table

Dec 12, 2000

When do you use temp table?.

View 2 Replies View Related

Temp Database

Jan 24, 2001

I'm running SQL Server 7.0 and in the morning received the following message
and immidiately after the server went for a reboot.

ERROR :
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth..

However,I fail to understand why I got the above error even though the temp database is configured to grow automatically by 10% and also why did the SQL
Server went for a reboot? Can anyone help me on this
Thanks!

View 1 Replies View Related







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