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
Microsoft SQL Server 2008MySQL ServerMicrosoft SQL Server
Last Comment
JoeNuvo
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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