Possible To Have Temporary Rows In A Non-temporary Table? Lifespan On Rows..
Mar 14, 2004
I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
I am using SQL 2000 I have the following code. When saving it, I am getting an error: There is already an object named '##tbl' in the database
This is although #tbl is dropped. Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int set @x=1 IF @x=0 begin create table #tbl ( abc int ) drop table #tbl end
IF @x=1 begin create table #tbl ( abc int ) drop table #tbl end
I have to create a temporary table for generating a report in VB. Pls help how to check the temporary table name in database. I want if exits than drop and create a new one.
The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below:
Description | Period 01 | Period 02 | Year to Date ========================================================= Sales | 20,000.00 | 10,000.00 | 30,000.00 Total Sales | 20,000.00 | 10,000.00 | 30,000.00
The above report would list 4 columns, with the last column being a calculated field as a sum of Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).
Guys, hope someone out there can help me with the sql command for the above report?
Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.
What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.
Declare @Temp Table(id_customer int, id_product varchar(10)) Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104') Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143') Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103') Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102') Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101') Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10') Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
select * from @Temp Declare @results VarChar(100) Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '') From @Temp where id_customer = 12 Select @results as all_products
and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(
Hi All, DECLARE @MyTableVar table( EmpID nvarchar(10) ) select login_id from cpm into @MyTableVar Above syntax is not working.... Actually i want to table variable and store the result returned by stored procedure. How to do that... Thanks and reagards A
I'm trying to drop a temporary table. I keep getting this error: Cannot drop the table '#temp_table', because it does not exist in the system catalog. I tried the following but it did not work, so help would be appreciated. if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
Hello once again, My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed. I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code. What are my options? Any help would be greatly appreciated. Pettrer
Im trying to find a table and Drop in T-SQL using this script.
/* Start */ Use Students IF exists (Select * from information_schema.tables where table_name ='##Exams_result) drop table ##Exams_result go Create table ##Exams_result..............etc
/* end */
But I cant find my temporary table on this way... Any sugestion?
Luiz Lucasi lc@culting.com Rio de Janeiro - Brazil
When we are writing a big query that against to 25+ tables (it has lots of lookup and lots of business logic in it so it will have lots of left outer joins, right outer joins, inner join, UNION, group by, and lots of SQL server functions) should we put everything in one select statement? Or we should create lots of temporary tables and then join them together?
My friend told me that I should use one statement instead of lots of temp tables in order to reduce the physical read. He does not like the temp table idea since there will be lots of user running the query in the same time and it will use lots of space. But I don’t believe that is true. (I believe when you put everything in one statement, SQL server still needs to create the result set in the temp db.) There will be lots of join going on and it could hurt the performance.
I believe that 1.We should create the temp tables and then join the temp tables together later to get the final results. we can create the index on the temp table at runtime. 2.If the order of columns in the index does not match to our where clause, then the index is useless. 3.If there is a SQL function in the where clause, the index is useless. (for example, where Datepart(yy, orderdate)=’1998’) 4.If there is a NOT in the where clause, the index is useless.
What do you think? Are you agree with me?
Following is the query. Is there any better way to do this?
Select d.Description as Division, c.Description as Region, b.StateCode as State, b.FacilityNumber, b.Name as FacilityName, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate,101) Else 'Pending' End) as PriorStdSurveyExit, (Case When a.ExitDate Is Not Null And a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+89,101) Else '' End) as ThreeMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+179,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+179,101) Else 'Pending' End) as SixMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+450,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+450,101) Else 'Pending' End) as FifteenMonthWindow From ( Select a.FacilityNumber, a.ExitDate, IsNull(b.FacilityNumber,0) as IJValid From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber ) a Left Join ( Select Distinct b.FacilityNumber From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join ComplianceRatingCode e on (a.ComplianceRatingCodeID = e.ComplianceRatingCodeID) Where a.VerbalResultDate is Not Null And (c.Description Like 'Standard%' And d.Description Like 'LTC%') And e.Description Like 'Category 4%' And a.ExitDate <= @InDate Union Select Distinct b.FacilityNumber From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join Citation f on (e.CitationID = f.CitationID) Inner Join SSLetterCode g on (f.SSLetterCodeID = g.SSLetterCodeID) Where a.VerbalResultDate is Not Null And d.Description Like 'LTC%' And a.ExitDate <= @InDate And g.SSLetterCode in ('J','K','L') Group By b.FacilityNumber ) a Right Join ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber
) b on (a.FacilityNumber = b.FacilityNumber) Where a.ExitDate >= b.ExitDate
) b on (a.FacilityNumber = b.FacilityNumber) ) a Right Join Facility b on (a.FacilityNumber = b.FacilityNumber) Left Join Region c on (b.RegionID = c.RegionID) Left Join Division d on (c.DivisionID = d.DivisionID) Order By d.Description, c.Description, b.FacilityNumber
when i run in query analyzer , using "exec SP_TEST" , it work and display result
but when run Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208" and show Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?
We are having some issues with temporary tables (with # prefixes) within Stored Procedures.
When running a profile trace on them, the stored proc quite happily creates the temp # table (in fact several of them) but whenever it hits the first statement inserting data into one of them (and it doesn't matter which one), there is a 5-6 second delay.
By commenting out one and moving to the next piece of code, the same thing happens.
Following which, the rest of the Stored Prco runs fine and subsequent inserts into the # temp tables also run efficiently.
Is the stored proc getting recompiled perhaps ?
Any advice woul;d be appreciated.
we are running SQL Server 7.0, dont know whether that helps ?
I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?
--Query 1
SELECT * INTO #MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] d WHERE TransferFromToProgram<>'' AND d.Status=12 ;
how you code to get a timestamps to a temp table.ex. select date from clock where name='name' and nbr='10';I want this data to be in my temp date table..
Hi, what im trying to do is to combine multiple SELECT staetment so that it could produce 1 dataset instead of 2(multiple). (fields and column are same, just different tblname). Okay, so im thinking of insert those into temp table.
1. select statement 1 > insert into #MyTemp 2. select statement 2 > i want to insert into #MyTemp too...
Problem is sql disallowed this. That's why im thinking of global temporary table, but dunno how to write tht :P. Pliz help. Thanks in advance
Hello all,I'm using SS2K on W2K.Brieffing: Many months ago, I created a stored procedure only used by thosewith admin rights in SS. Now, someone else (without admin rights) has to runit. I gave him rigth to execute the SP but, at the second and moreexecution, he got a error message concerning a temp table already existing(see further).The SP:------------------------------------------------------CREATE PROCEDURE MySP@Type INTDECLARE @strSQL AS VARCHAR(4000)IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHEREtable_name = '##MyTmpTable')DROP TABLE ##MyTmpTableSELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROMMyTable'EXECUTE(@strSQL)IF @Type = 1SELECT MyField1FROM ##MyTmpTableELSE IF @Type = 2SELECT MyField2FROM ##MyTmpTableELSESELECT MyField3FROM ##MyTmpTableGO------------------------------------------------------The error I got on the second time the user run the sp is: "Table##MyTmpTable already exists." The front-end where this SP is run is A97.That's where I got this message. This SP looks like a simple SELECT queryfrom A97 users perspective.Please, do no argue about the way of doing the work done! It is simplifiedat most in order to make it short and easy to read. I have to use thecommand "EXECUTE(String)" and, because of this, I connot use a localtemporary table instead of a global one.I suspect non-admin user cannot drop global temporary table, but the errormessage makes me believe that this code line is not even run, as if thecondition "IF EXISTS(...)" return false even if the table actualy exists.Anybody can help about this? What should I do to solve this problem?Yannick
Hi,How can I create a temporary table say "Tblabc" with column fieldsShmCoy char(2)ShmAcno char(10)ShmName1 varchar(60)ShmName2 varchar(60)and fill the table from the data extracted from the statement..."select logdetail from shractivitylog"The above query returns single value field the data seperated with a '·'Ex:BR··Light Blue Duck··in this case I should getShmCoy = 'BR'ShmAcno = ''ShmName1 = 'Light Blue Duck'ShmName2 = ''I want to do this job with single SQL query. Is it possible. Pls help.Herewith I am providing the sample dataBR··Light Blue Duck···0234578···BR··Aqua Duck···0234586···UB··Aqua Duck··Regards,Omav
I have a stored procedure that creates a temporary table.If two people (using the same account) execute the same storedprocedure at the same time, will they overwrite / access the temporarytable ?Calista.
Hi,I have a stored procedure, that works perfectly when run from the queryanalyser, however if I run it through access vba, (using exec) I get aruntime error 208: invalid object name '#tmpContact'.Any ideas why this happens? The temporary table #tmpContact is used inthe procedure, but as I say, it all works fine from the analyser.Thanks,Chris
XML_NAME | XML_VALUE YearOfPublish | 2007 IssueNumber | 28 Autor | John
so I need rezalt that it give me all in one row similar to YearOfPublish | IssueNumber |Autor 2007 | 28 | John
Yes probably it is 100 times that somebody ask this king of question but I need it to make dynamically ... because the name of columns and it number can be changed in any time ...so I need to go trought all XML_NAME columns rows and link it with XML_VAlUE colum and give me all in one row...I hope I was clear enouf
I start something like this
CREATE TABLE [#headerTemp] -- It is not good by I don't know how to create temporary table dynamically trough some selection ( [id] [int] IDENTITY , [YearOfPublish] [varchar](100), [IssueNumber] [nvarchar](10), [Autor] [nvarchar](50) );
--insert into #headerTemp (id) values(1); //I insert first row in order to update ...but without any sussces
DECLARE HeaderData_Cursor CURSOR FOR SELECT def.NODE_NAME, m.value FROM EA_MAIL_DATA AS m INNER JOIN EA_XML_DEF AS def ON m.id_node = def.ID WHERE (id_parent = @id_node) AND (id_mail = @id_mail);//this is select statment that give me all neded data OPEN HeaderData_Cursor;
set @sqlValues = '';
FETCH NEXT FROM HeaderData_Cursor INTO @columnHeader, @headerValue; WHILE @@FETCH_STATUS = 0 BEGIN
set @sqlHeader = RTrim(LTrim(Convert(varchar(80),@columnHeader)))-- RTRIM(@columnHeader);-- Convert(nvarchar(50), set @sqlValue = Replace(@headerValue,' ','_')
set @sqlValues = @sqlValues +''''+ @sqlValue+''''+',';
FETCH NEXT FROM HeaderData_Cursor INTO @columnHeader, @headerValue; END; CLOSE HeaderData_Cursor; DEALLOCATE HeaderData_Cursor;
set @sqlValues = SUBSTRING(@sqlValues, 0, LEN(@sqlValues) ) print @sqlValues
insert into #headerTemp values( Convert(varchar(80),@sqlValues) ) // IT DONT WORK ....
select * from #headerTemp
------------------
So my problem is perhaps that I don't know how to make correct SQL stetment with parameters and this ''' thing because always say that number of columns is not ok ...
I have few stored procedures which I need to pull reports from. They all work with Temporary tables (#Name1). I am writing a wrapper kind of a SP which will call these SP's based on some inputs. When first I created a dataset with this SP, I got errors that the # tables were not found. I thought using temporary tables is not supported with SRS 2000. But now again I tried and it worked! The only difference now is that the wrapper SP now is on the same machine as the SRS and in the earlier case, it was not. Does that make any difference? Ultimately, will the combination of temporary tables and SRS 2000 work or not?
Right now I am writing to a sql table during a process where the end user starts entering the contents for a file that is going to be generated once he finishes entering the data, but the problem is that if more than one user is doing the same process the data would get mixed up. To avoid this I thought in creating a temporary table (its name will consist of a string and the current date time).
I would like to see any tutorial about creating and working with temp tables. Or if you have any suggestions, I will appreciate them. Thanks
I have a temporary table in a stored procedure and the table contains a single value I need to retuen from the stored procedure How do I get the value?
We are trying to use a ## table (global temporary table) in Access or Excel thru a ODBC connection to tempdb. We are able to see the system tables, but not any ## tables. We are able to perform selects on the same ## table with the same userid and password in Query Analyzer. If we use sa or make the userid dbo we are able to see the ## tables, but I don't want to give these type of permissions.
Can anyone explain why am I getting this error? I don't really see what's wrong with the SQL script... I'm a little bit puzzled by this...
The error I got is ... --snip-- Msg 2714, Level 16, State 6, Procedure sp_DailyTopRecords, Line 107 There is already an object named '#DailyTopTenRegionalTmp1' in the database. --snip--
Code:
BEGIN SELECT [Name] INTO #tmpRegions FROMtblvRegions WHERERegionID <= 10
--Loop Through Regions WHILE EXISTS (SELECT TOP 1 * FROM #tmpRegions) BEGIN --Get RegionName SELECT @RegionName = (SELECT TOP 1 [Name] FROM #tmpRegions)
SELECT TOP 10Year, MakeID, ModelID, [Name] INTO #DailyTopTenRegionalTmp1 FROM vwAvgSale WHERESaleType = 'R' AND --Retail... SoldDate >= @SearchDate AND [Name] = @RegionName GROUP BYYear, MakeID, ModelID, [Name] ORDER BYCOUNT(SoldID) DESC
IF @@ERROR <> 0 BEGIN SET @ErrorCode = @@ERROR SET @ErrorDescription = 'Could not insert into #DailyTopTenRegionalTmp1' SET @ErrorRecord = 0 GOTO ERROR_HANDLER BREAK --Break the while loop... END
DELETE FROM #tmpRegions WHERE [Name] = @RegionName END END