none
row count for each table RRS feed

  • Question

  • Hi All,

    The following code extracts data from different tables and save output data as text files.

    I want to insert row count into a separate table.

    Somehow following code does not give me the correct row counts.

    Can anyone tell me what I am doing wrong here pls?

    SET NOCOUNT ON 
    declare @FileName nvarchar(1000)
    declare @TableName nvarchar(1000)
    declare @sql nvarchar(1000)
    declare @Rows int
    set @FileName =?
    set @TableName = ?
    select @sql = 'bcp "SELECT TOP 10 * FROM '+@TableName+' " queryout Y:\RejFolder\'+@FileName+''+'.xls -c -e -t, -T -S 1xx.xx.2xx.1xx'
    exec master..xp_cmdshell @sql
    select @Rows = @@ROWCOUNT
    INSERT INTO [HSN].[dbo].[RejectLog]
    ([TableName]
    ,[RejCode]
    ,[RecordsCount]
    ,[EmailSendto]
    ,[RejDate]
    ,[CreateDt]
    ,[Sucess])
    VALUES
    (@TableName
    ,2
    ,ISNULL(@Rows,0)
    ,'pam@test.com'
    ,GETDATE()
    ,GETDATE()
    ,'y')
    GO

     

     


    shamen
    Tuesday, December 21, 2010 11:32 PM

Answers

  • I'm guessing you can add

     

    declare @SQL nvarchar(max)
    
    set @SQL = 'select @Rows = count(*) from  ' + quotename(@TableName)
    
    execute sp_ExecuteSQL, N'@Rows int output', @Rows OUTPUT
    
    
    

    to get the number of rows right after bcp command.


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Wednesday, December 29, 2010 8:41 AM
    Wednesday, December 22, 2010 5:26 AM
    Moderator

All replies

  • What row count you're expecting to get here? The 10 records you selected? You can try to print @Rows to see what does this statement return. 

    For me the above returns 7 records with this info (and that number is captured):

    NULL

    Starting copy...

    NULL

    10 rows copied.

    Network packet size (bytes): 4096

    Clock Time (ms.) Total     : 1      Average : (10000.00 rows per sec.)

    NULL


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, December 21, 2010 11:58 PM
    Moderator
  • Yes right now I only selected 10 rows. But i will remove top 10 part after i fix the error.

    I am also getting 7. I use this code in one of my SSIS package. It is supposed to extract data from 5 different tables and save it as text files.

    I want to get the row count for each data extracts.

    is there any other way I can do this?

     

    Thanks


    shamen
    Wednesday, December 22, 2010 5:21 AM
  • I'm guessing you can add

     

    declare @SQL nvarchar(max)
    
    set @SQL = 'select @Rows = count(*) from  ' + quotename(@TableName)
    
    execute sp_ExecuteSQL, N'@Rows int output', @Rows OUTPUT
    
    
    

    to get the number of rows right after bcp command.


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Wednesday, December 29, 2010 8:41 AM
    Wednesday, December 22, 2010 5:26 AM
    Moderator