March 10, 2004 at 5:45 pm
1) What's the best way to create a temp table in a proc? I need the proc to return results from the temp table. I am concerned that if I create the table in the proc then someone else runs the proc they will get a 'table already exists' error.
2) How would I select/update the first x amount of rows based on a datetime? Example: I have 20 rows that contain dt fields. I want to update the first 5 (first being the ones w/ the earliest dates).
March 10, 2004 at 7:36 pm
1) select columnlist into #temp from sampletable where 1=2
(temp tables are kept unique to the connection by the server)
2) Look up SET ROWCOUNT and/or usage of TOP w/ORDER BY in the books online
March 10, 2004 at 7:44 pm
The answer to the first question is to use true temp tables instead of tables that you are just going to delete. Place a pund symbol (#) in front of the name of the table to tell SQL to create the table in the TEMPDB database. This way the table is only visable to the session creating it and it is deleted after the session is terminated.
create table #MyTable ( field1 int, field2 char(50), field3 datetime)
select * from #mytable
Here's one way to get the results you are looking for from the second question.
select * from yourtable join (select top 5 datefield from yourtable order by datefield) as filter on yourtable.datefield = filter.datefield
Sorry about the lack of formating. This editor is driving me nuts. I'll have to get back into HTML coding again. Hope those help
Thanks,
Carl
March 11, 2004 at 12:08 am
This answer if for the second question
update test set Date_col = #DateVal# where pk in
(select top 5 pk from test order by Date_col desc)
PK = Primary key column in the test table
Date_col = Date column in the test table
March 11, 2004 at 2:56 am
Answer1.
If you are thinking of creating temp table in a stored proc, the best way is to create memory tables rather than temp tables. This is because, memtables give better performance when compared to temp tables.
----------------------------------------------------------------
(
OrderID int,
OrderDesc varchar(100)
)
Insert into @TempList values(2,'Second Order')
----------------------------------------------------------------
In the above example the keyword 'TABLE' is the datatype of the local variable @temp. As soon as your storedproc execution finishes, the memtable will be dropped automatically.
However, there is one problem with memtables.....You can not execute dynamic SQL strings to insert data into these tables.
Answer2.
Update your_table
Set your_field_to_update = new_value
Where your_table_PK in (Select Top 5 your_table_PK from your_table order by your_table_date_field desc)
Hope this helps.....Cheers
March 11, 2004 at 6:41 am
Further to the first part of phanikks answer. If you use a table variable in a statement involving a join you must give the table variable an alias e.g. myTable Join @tempTable tmp On myTable.tid = tmp.fid
otherwise the query tends to fail.
March 11, 2004 at 6:56 am
Rob
Check out the SQL Books Online for DECLARE (as in DECLARE @mytab TABLE...) and SELECT TOP n (re: your requirement of selecting only certain dates)
Cheers,
Ken
BTW: I recommend in-memory tables for stored procedures. There are [certain] cases where you're forced to use temp tables, but in that case just make sure you use a DROP TABLE statement any place you exit the proc.
March 11, 2004 at 8:18 am
Thank you all for your suggestions and help. It fixed me up just great and my performance is much better. I appreciate you taking the time as it has helped me tremendously!
March 18, 2004 at 8:17 am
First off I want to say that the TableVariable does not guarantee better performance, you have to find out yourself depending on your query. One big difference to #tables is that you cannot use indexes on them. There has been some very technical and highly informative discussions about the differences between the TableVariable and #table in this forum but I do not seem to find it now.
About table variable BOL:
A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.
Second. If you are using #tables you do NOT always have to drop them. BOL:
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
And based on my experiences it is most of the time better if you can avoid 'user' tmp storage (not the ones that SQL query analyzer automatically produces as a result of some queryplans) and #tables are usually better for big resultsets (kinda like BOL suggested).
Regards, Hanslindgren!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply