In a stored procedure, when is #Temptable created in SQL Server 2005? When creating the query execution plan or when executing the stored procedure? if (@x = 1) begin select 1 as Text into #Temptable end else begin select 2 as Text into #Temptable end ...
If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this: "the table must contain data when the index is created. If you create the temporary table and create the index on an e...
I just want a quick way (and preferably not using a while loop)of createing a table of every date between date @x and date @y so I can left outer join to some stats tables, some of which will have no records for certain days in between, allowing me to mark missing days with a 0 ...
I'm logged into a SQL Server 2005 database as a non-sa user, 'bhk', that is a member of the 'public' server role only. The following code tries to execute within a stored procedure called by user 'bhk'. This line of code... TRUNCATE TABLE #Table1 DBCC CHECKIDENT('#Table1', RESEED, @SequenceNumber) WITH NO_INFOMSGS causes this error......
Is there any way of determining whether or not a specific temp table has been created in a session without referencing the tempdb database that it was created on? Users are allocated to a specific tempdb when they log in, so I don't know which tempdb they'll be using. I don't need to specify a tempdb to select data out of the temp table...
I've created a stored procedure similar to the one below (I'm using this cut down version to try and figure our the problem). CREATE PROCEDURE bsp_testStoredProc AS BEGIN CREATE TABLE #tmpFiles ( AuthorName NVARCHAR(50), PercentageHigh INT ) -- Insert data into temp table SELECT AuthorName, PercentageHigh FROM #tmpFiles ORDER ...
Hi, I read an excel sheet into a datagrid.From there , I have managed to read the grid's rows into a DataTable object.The DataTable object has data because when I make equal a grid's datasource to that table object , the grid is populated. My Problem : I want to use the table object and manipulate its values using SQL server,(i.e. I wan...
I'm familiar with Sybase / SQL server, where I can create a temp. table like this: SELECT * INTO #temp FROM tab1 , tab2 WHERE tab1.key = tab2.fkey SELECT * FROM #temp WHERE field1 = 'value' #temp only exists for the duration of this session, and can only be seen by me. I would like to do a similar thing in Ora...
In essence I'd like to store some rows in a temporary variable for the life of a procedure in MySQL. My procedure will grab a column of foreign keys at the beginning of the procedure. Once I'm done working with them I want to update the table to indicate that they have been processed. There may be inserts into this table while my proced...
Hi folks, I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on. The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN This basically loo...
I have heard these two terms "temporary table" and "global temporary table" used pretty much in similar context. What is the difference between the two? ...
I'm using SQL Server 2000, and many of the stored procedures it use temp tables extensively. The database has a lot of traffic, and I'm concerned about the thread-safety of creating and dropping temp tables. Lets say I have a stored procedure which creates a few temp tables, it may even join temp tables to other temp tables, etc. And le...
Hi again, I need to perform a query 2.5 million times. This query generates some rows which I need to AVG(column) and then use this AVG to filter the table from all values below average. I then need to INSERT these filtered results into a table. The only way to do such a thing with reasonable efficiency, seems to by creating a TEMPORA...
I am trying to find out just how much temporary space each session connecting to my database is using, but I've not been able to figure it out. I am trying to determine if the number of extents in use by a particular session are overly high. I tried looking at V$TEMP_EXTENT_POOL and V$TEMP_EXTENT_MAP, but cannot seem to find anything wh...
Hi, I am using the following code to check if the temp table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give error saying "invalid column". Please let me know what I am doing wrong. IF OBJECT_ID('tempdb..#Results') IS NOT NULL D...
Is there a way to define a temp table without defining it's schema up front? ...
Which are more performant, CTE or temporary tables? ...
I've got a users table and a votes table. The votes table stores votes toward other users. And for better or worse, a single row in the votes table, stores the votes in both directions between the two users. Now, the problem is when I wanna list for example all people someone has voted on. I'm no MySQL expert, but from what I've figure...
I have a table with some duplicate entries. I have to discard all but one, and then update this latest one. I've tried with a temporary table and a while statement, in this way: CREATE TABLE #tmp_ImportedData_GenericData ( Id int identity(1,1), tmpCode varchar(255) NULL, tmpAlpha3Code varchar(50) NULL, tmpRelatedYear ...
hi all, I m having a stored procedure which returns two result sets based on the success or failure. SP sucess result set: name, id ,error,desc SP failure result sret: error,desc I m using the following query to get the result of the stored procedure .It returns 0 for success and -1 for failure. declare @ret int DECLARE @tmp TABL...