Is it possible to add a header record which contains the total count of table and put it on top of the report and just using SELECT statement? If so, how do we do that? I'm doing 2 SELECTs, first with count(*) then followed by the real SELECT statement. I open the file that was created by QA and I modified the file since there are strings I don't need such as (1 row(s) affected), those hundreds of minus symbols. Thanks, Neil
use count function to get total number in that group. count(unique column name) in group header or group footer.
I was looking for an output that would produce a textfile similar to the output below 2009101600003640 Frank|Adams|Male|36 Susan|Adams|Female|34 Beth|Durkee|Female|39 John|Smith|Male|37 ... and so on The first 8 digits are for the current date 3640 is the total count of the table
Generating the header line is straightforward. SELECT CONVERT(varchar, GETDATE(), 112) + REPLACE(STR(COUNT(*), 8), ' ', '0') FROM dbo.table However, the trouble comes when you need to merge this header row with the rest of the query that produces the actual output. Looking at your other thread http://sql-server-performance.com/Community/forums/t/31026.aspx I'm inclined to think that having an SSIS package in which one task generates the file and writes the header row and a second one just appends the pipe delimited output might be better suited.
Here is what I did last week. 1. created a temporary table with 2 columns, id and strvalue 2. inserted the output of date and count 3. inserted the result of my other final sql query 4. did a select on the temporary table and ordered it by id asc 5. Results set to file I was looking at bcp but wasn't sure if it can append to an existing file. Thanks for the other idea.
