Solved

count Number of Items in a temp table

Posted on 2010-09-07
5
444 Views
Last Modified: 2012-05-10
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
Comment
Question by:ZURINET
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33616345
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
 

Author Comment

by:ZURINET
ID: 33616552
Hi prakash

I have more than 200 users accessing the query concurrently.

how can Global Temporary Table help me..?
0
 
LVL 5

Expert Comment

by:spikelly
ID: 33617356
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 33617720
You can get row count from @@rowcount, getting it after you execute statement

decalare @cnt int
exec(@sql_trans)
set @cnt=@@rowcount
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 33670298
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question