SQL Server Performance

header record

Discussion in 'General Developer Questions' started by v1rt, Oct 16, 2009.

  1. v1rt New Member

    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
  2. satya Moderator

    use count function to get total number in that group.
    count(unique column name) in group header or group footer.
  3. v1rt New Member

    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
  4. FrankKalis Moderator

    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.
  5. v1rt New Member

    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. :)

Share This Page