none
Insert a count(*) into a temp table

    Question

  • Hi all, 

    Using SQL 2005 what would be the easiest way of inserting a count of rows into a temp table?  My sp 

    is being driven by looping through all the [name] values returned by sys.tables via a cursor.  What I'm not sure of is 

    how to form the INSERT command: 

    This is what I have so far. 

    CREATE TABLE #Cardinality 
    		(TableName VARCHAR(100), RowCountWMSRO BIGINT, RowCountWMS2SQL BIGINT)
    	 
    	
    		DECLARE TablesCursor CURSOR FAST_FORWARD 
    		FOR 
    			SELECT TOP 100
    				[name] 
    			FROM
    				[WMSRO].sys.tables
    			
    --			WHERE 
    --				[name] = 'ORD_MAST'
    		
    			ORDER BY 
    				[name]
    			FOR READ ONLY; 
    	
    	
    	OPEN TablesCursor 
    	FETCH TablesCursor INTO @TableName 
    
    	WHILE (@@FETCH_STATUS = 0) 
    	BEGIN 
    		PRINT @TableName 
    		
    		SET @Cmd1 = 'Select count(*) from [WMSRO].dbo.' + @TableName
    		--SET @Cmd2 = 'Select count(*) from [WMS-2-SQL].dbo.' + @TableName
    		
    		
    		
    		INSERT INTO #Cardinality (TableName , RowCountWMSRO) 
    		VALUES 
    			(
    				@TableName, 
    				SELECT COUNT(*) FROM [WMSRO].dbo.ORD_MAST
    				   		    
    			 )
    	
    	
    
    		FETCH TablesCursor INTO @TableName 
    	END 
    
    	CLOSE TablesCursor 
    	DEALLOCATE TablesCursor 	
    		
    	DROP TABLE #Cardinality

     

     

    Regards

    Dave 

     

     

     

    Wednesday, May 12, 2010 1:24 PM

Answers

  •  

    You can use sp_MSForEachTable to get the record count of each table in the database....

    CREATE TABLE #TableRecCount (TableName VARCHAR(250), RecCount BIGINT)
    
    EXEC SP_MSForEachTable N'
    INSERT INTO #TableRecCount
    SELECT ''?'', COUNT(*)
    FROM ? (nolock)' --NOLOCK HINT TO REDUCE BLOCKING
    
    SELECT * FROM #TableRecCount 
    • Proposed as answer by Naomi NModerator Wednesday, May 12, 2010 2:08 PM
    • Marked as answer by york0001 Tuesday, May 18, 2010 8:08 AM
    Wednesday, May 12, 2010 1:58 PM

All replies

  • You could use INSERT INTO ... EXEC to insert the result of the dynamic batch.

    ...

    SET @Cmd1 = 'Select tn = @TableName, cnt = count(*) from [WMSRO].[dbo].' + QUOTENAME(@TableName)

    INSERT INTO #Cardinality (TableName , RowCountWMSRO)
    EXEC sp_executesql @cmd1, N'@TableName sysname', @TableName;

    ...

    Be aware that both variables (@cmd1, @TableName) haven't  been defined in the script.

    AMB

    Wednesday, May 12, 2010 1:33 PM
    Moderator
  • Pls try this,

    Create table tblCount(TableId int,TableName varchar(100),RecordCount int)
    go
    Insert into tblCount
    Select id,name,0 from Sysobjects where type = 'u'

    go

    Select 'Update tblCount set RecordCount = ( Select count(1) from ' + Tablename + ') where TableId = ' + Cast(Tableid as varchar(10)) from tblCount

    ---

    Execute the update query.

     

    Wednesday, May 12, 2010 1:44 PM
  •  

    You can use sp_MSForEachTable to get the record count of each table in the database....

    CREATE TABLE #TableRecCount (TableName VARCHAR(250), RecCount BIGINT)
    
    EXEC SP_MSForEachTable N'
    INSERT INTO #TableRecCount
    SELECT ''?'', COUNT(*)
    FROM ? (nolock)' --NOLOCK HINT TO REDUCE BLOCKING
    
    SELECT * FROM #TableRecCount 
    • Proposed as answer by Naomi NModerator Wednesday, May 12, 2010 2:08 PM
    • Marked as answer by york0001 Tuesday, May 18, 2010 8:08 AM
    Wednesday, May 12, 2010 1:58 PM
  • Check out the following methods of counting rows in all tables:

    http://www.sqlusa.com/bestpractices2005/alltablesrowcount/

    The fast methods are more or less accurate.

    The accurate method on the other hand slow and has considerable overhead.

    Let us know if helpful.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, May 12, 2010 1:59 PM
    Moderator
  • CREATE TABLE #cardinality (
      table_name sysname
     , row_count int
    )
    
    DECLARE @cmd varchar(300)
    
    DECLARE table_cursor CURSOR FOR
     SELECT TOP 100
         'INSERT INTO #cardinality (table_name, row_count) SELECT ' + QuoteName(name, '''') + ', Count(*) FROM ' + QuoteName(Schema_Name(schema_id)) + '.' + QuoteName(name)
     FROM  sys.tables
     WHERE name LIKE '%a%' --Add your own criteria here if required
    
    OPEN table_cursor
    
    FETCH NEXT FROM table_cursor INTO @cmd
    
    WHILE @@Fetch_Status = 0
     BEGIN
      EXEC (@cmd)
      FETCH NEXT FROM table_cursor INTO @cmd
     END
    
    CLOSE table_cursor
    DEALLOCATE table_cursor
    
    SELECT table_name
       , row_count
    FROM  #cardinality
    
    GO
    DROP TABLE #cardinality

    How's this one for size:

     

     

     

     


    George
    Wednesday, May 12, 2010 3:11 PM
    Answerer

  • Hi, 

    Thanks for all the answers.  :-) 

     

    I'm going to try this one as it looks pretty straight forward and counts rows in different databases. 

    Regards

    Dave

     

     

     

    You can use sp_MSForEachTable to get the record count of each table in the database....

    CREATE TABLE #TableRecCount (TableName VARCHAR(250), RecCount BIGINT)
    
    EXEC SP_MSForEachTable N'
    INSERT INTO #TableRecCount
    SELECT ''?'', COUNT(*)
    FROM ? (nolock)' --NOLOCK HINT TO REDUCE BLOCKING
    
    SELECT * FROM #TableRecCount 
    

    Wednesday, May 12, 2010 3:53 PM
  • Any progress?


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, May 17, 2010 4:25 PM
    Moderator
  • Try

     

    CREATE TABLE #TableRecCount (TableName VARCHAR(250), RecCount BIGINT)
    
    declare @SQL varchar(max)
    select @SQL = coalesce(@SQL + CHAR(13) + CHAR(10),'') + '
    
    INSERT INTO #TableRecCount
    SELECT ' + quotename(Table_Name,'''') + ' as TableName, COUNT(*)
    FROM ' + quotename(Table_Schema) + '.' + quotename(Table_Name) + ' (nolock)' from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME
    execute (@SQL)
    
    select * from #TableRecCount 
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 17, 2010 4:32 PM
    Moderator