Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

count Number of Items in a temp table

Hi all  
I have a temp table #myTable

 I am looking for a way to do this
      select @intcount = count(*)            
      from #myTable
Note I have a conditional processing away from the temp Table. i.e
Create table  #LCHItems(bla, bla)
Insert into #myTable  exec(@sql_trans)

if (myVariable = newVar)
begin
select @intcount = count(*)            
      from #myTable
end

else


begin
end
0
ZURINET
Asked:
ZURINET
1 Solution
 
Om PrakashCommented:
Use Global Temporary Tables
Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables

More info/example:
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
0
 
ZURINETAuthor Commented:
Hi prakash

I have more than 200 users accessing the query concurrently.

how can Global Temporary Table help me..?
0
 
spikellyCommented:
In that case use Table Variable.
Syntax : DECLARE @myTable TABLE (var1 int, var2 varchar, etc...)
This is faster and create less locking on the server. the table is dropped automatically at the end of the statement execution.
More info here:
1- http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
2- http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Regards.
0
 
Lara FEACommented:
You can get row count from @@rowcount, getting it after you execute statement

decalare @cnt int
exec(@sql_trans)
set @cnt=@@rowcount
0
 
JoeNuvoCommented:
just uses @@rowcount as above comment

Create table  #LCHItems(bla, bla)
Insert into #myTable  exec(@sql_trans)
SET @intcount = @@ROWCOUNT

then you can have value of @intcount right away

if this still can't work it out in your situation, you should provide more additional details
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now