Solved

count Number of Items in a temp table

Posted on 2010-09-07
5
443 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

751 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