How do I count the records I've just UNLOADed?

[Former Member]

I want to dynamically create a SELECT statement, then use EXECUTE IMMEDIATE and UNLOAD to write to a file... and I'd like to know how many records I've just written.

Running 9.0.2.3850

This is what I've got...

CREATE  PROCEDURE "rhiner"."rowcount_test"()

BEGIN

DECLARE @select_statement long varchar;
DECLARE @output_filename long varchar;
DECLARE @output_directory long varchar; 
DECLARE @output_options long varchar;
DECLARE @unload_statement long varchar;

set @output_directory = 'd:\\\\TEMP\\\\';
SET @output_options = ' DELIMITED BY ''\\x09'' FORMAT ASCII QUOTES ON' ;

--  write first file

SET @select_statement = '  SELECT  * FROM SYS.SYSTABLE ' ;
        SET @output_filename =  'first_file.TXT ';;
        SET @unload_statement = 'UNLOAD ' || @select_statement  ||  
            ' TO ''' || @output_directory || @output_filename  || ' '' ' ||
            @output_options || ';'  ;
        message @unload_statement to client;
        EXECUTE IMMEDIATE @unload_statement; 
       message @@rowcount || ' Records unloaded to first file.'  to client;

-- then second file 
          SET @select_statement = '  SELECT  * FROM SYS.SYSTABLE  where table_name like ''sys%'' ' ;;
        SET @output_filename = 'second_file.txt';
        SET @unload_statement = 'UNLOAD ' || @select_statement  ||  
            ' TO ''' || @output_directory || @output_filename  || ' '' ' ||
            @output_options || ';'  ;
        message @unload_statement to client;
        EXECUTE IMMEDIATE @unload_statement; 
        message @@rowcount || ' Records unloaded to second file.'  to client;

END

So, now that I've writen two files, I'd like to know how many records are out there. My first thought is to use @@Rowcount -- which I've incldued in the code above. But that value does not get updated during this procedure... so what ever was in there before the procedure runs is what I get in the messages while the procedure is running.

The only other way I can think of is to run queries against the db tables again to get a record count, but that won't tell me how many records I just wrote out the the file.

Is there a better way?

thanks!

[VolkerBarth]

You could run an additional select statement just to count the records in the result set. (Of course, that would not really count how many rows are unloaded but it should return how many have to be unloaded, and that should match exactly as long as UNLOAD works correct.)

It omits the copying of the rows in contrast to Mark's suggestion. Whether it is more expensive will depend on the complexity of the SELECT statement.

Basically you can use your SQL statement as a derived table and use SELECT COUNT(*) over that, such as

SELECT COUNT(*) INTO @cnt FROM (<your SQL statement>) S

(Note: Each column of your SQL statement is supposed to have an actual column name AFAIK, so for computed columns, you will have to use an alias.)

As you use a dynamic SQL statement, the SELECT COUNT(*) must be run with EXECUTE IMMEDIATE, too.

[The following 2 sentences are not true - see Mark's comment]:

In order to get the number into @cnt, you can't use an ordinary local variable as that would not be accessible in the EXECUTE IMMEDIATE statement. However, you can use a CREATE VARIABLE-created one.

Simplified sample code with a local variable:

BEGIN
  DECLARE @select_statement long varchar;
  DECLARE @count_statement long varchar;
  DECLARE @cnt integer;
  SET @select_statement  =
    'SELECT  * FROM SYS.SYSTABLE  where table_name like ''sys%'' ORDER BY 1';
  SET @count_statement =
    'SELECT COUNT(*) INTO @cnt FROM (' + @select_statement + ') S';
  EXECUTE IMMEDIATE @count_statement;
  MESSAGE 'Number of records: ' || @cnt;
END;

[MarkCulp]

Your statement about needing to use CREATE VARIABLE is not correct. As long as the EXECUTE IMMEDIATE statement is a single statement then the statement runs in the same context as the calling block - if it is a compound statement, then it runs as a batch an hence runs within its own scope and would not have access to the calling scope's variables.

[MarkCulp]

... To continue. Here is an example (no formatting unfortunately): begin declare @c int; declare @s long varchar; set @s = 'set @c = ( select count(*) from systable )'; execute immediate @s; select @c; end;

[VolkerBarth]

@Mark: Thanks for your correction and explanation! I had originally tested with an EXECUTE IMMEDIATE statement WITH RESULT SET ON (and a statement block), and in that case a local variable was not applicable. I wasn't aware of the difference.

[MarkCulp]

Note that @@rowcount is only updated on statements that affect rows within the database - INSERT, UPDATE, or DELETE - and is not updated by a SELECT or UNLOAD statement.

There is currently no direct way of knowing how many rows were written to the file by the UNLOAD ... SELECT statement.

One method would be to first select the rows into a temp table and then output the rows from the temp table as well as count the number of rows in the temp table. E.g.

BEGIN
  DECLARE @count int;

SELECT *
    INTO #temp
    FROM sys.systable;
  SET @unload_statement = 'UNLOAD SELECT * FROM #temp to '''
              || @output_directory || @output_file || ''' '
              || @output_options;
  execute immediate @unload_statement;
  SET @count = ( select count(*) from #temp );
END;

The drawback to using the temp table is that all rows need to be copied. If the data set is small then this may not be a concern but if you are unloading millions of rows (or very wide rows) and the data set does not all fit into the server cache then this operation may take some time to complete.

A second method would be to read the contents of the file that was just written and count the number of lines (i.e. count the number of \ characters).

HTH