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.
This is what I've got...
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?
asked 25 Mar '10, 19:54
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.
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 \x0A characters).
answered 26 Mar '10, 01:13
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
(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: