2 Questions please

Question

Post reply

2 Questions please

Rob Sawnic

SSC Eights!

Points: 940
More actions
March 10, 2004 at 5:45 pm

#152068

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).

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

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

Carl Reeds Newbie Points: 8 More actions · Answer 2

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

Kumaravelu SSC Rookie Points: 25 More actions · Answer 3

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

u2kota SSChasing Mays Points: 601 More actions · Answer 4

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.

----------------------------------------------------------------

Example:

DECLARE @temp TABLE

(

OrderID int,

OrderDesc varchar(100)

)

Insert into @TempList values(1,'First Order')

Insert into @TempList values(2,'Second Order')

Select * FROM @TempList

----------------------------------------------------------------

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

yellowleonard SSC-Addicted Points: 402 More actions · Answer 5

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.

Ken Klaft-80806 Say Hey Kid Points: 693 More actions · Answer 6

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.

Rob Sawnic SSC Eights! Points: 940 More actions · Answer 7

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!

Hans Lindgren SSChampion Points: 10496 More actions · Answer 8

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:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

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!