ZURINET
asked on
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
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
ASKER
Hi prakash
I have more than 200 users accessing the query concurrently.
how can Global Temporary Table help me..?
I have more than 200 users accessing the query concurrently.
how can Global Temporary Table help me..?
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.
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.
You can get row count from @@rowcount, getting it after you execute statement
decalare @cnt int
exec(@sql_trans)
set @cnt=@@rowcount
decalare @cnt int
exec(@sql_trans)
set @cnt=@@rowcount
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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