Adding a row count record at the bottom of a table

Question

Post reply

Adding a row count record at the bottom of a table

Coconut

Ten Centuries

Points: 1087
More actions
September 27, 2007 at 4:36 pm

#175242

I have to export a table into a flat file and at the bottom of the file, I need to have a record that show the total record count

for example) 2,894 rows

So, I did as below after the target table is available, hoping that it will insert the row count record at the bottom.

Insert Into dbo.ACSExport (Blank, ProviderId, RecipientId, Indicator, Date)

Select ' ', rtrim(convert(varchar,@v_cnt)), 'rows', '',''

But when I exported the table to a flat file, the record count row was in the middle of the file.

When I looked at the table in SQL without using ORDER BY, it also showed the record in the middle of the table?

Is there anyway to fix this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

What are you using to do the actual export?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Coconut Ten Centuries Points: 1087 More actions · Answer 2

Coconut

Ten Centuries

Points: 1087

September 27, 2007 at 7:56 pm

#736990

Jeff,

I am using the Export Wizard.

Thanks

tsnarr Valued Member Points: 59 More actions · Answer 3

tsnarr

Valued Member

Points: 59

September 27, 2007 at 9:48 pm

#737003

Whenever I need to do this I create an export table that has 3 columns. My identity, sort order and text. I insert the file data into the text column, with sort order of 1, then insert by record count in the last row with sort order of two. Then order by sort.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Max (9/27/2007)

Jeff,

I am using the Export Wizard.

Thanks

Don't know much about that in 2k5 and I usually don't use DTS in 2k.

One of the "problems" (and, advantages) of RDBMS's, is that there is no guarantee of any row order unless you take some action to guarantee the order. Tsnarr has one method above... the other might be to collect your reporting data, using Order By, into a temp table that has an IDENTITY column (as a PK). Instert all the reporting data, then insert the total you want... if the total is the last thing you insert, then the IDENTITY value for that row is absolutely guaranteed to be the largest number in a sort.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Coconut Ten Centuries Points: 1087 More actions · Answer 5

Thanks,

I was thinking about creating a clustered index on an idenitity column, but then I decided to export the table from a query. I guess it's the quickest and easiest way for now.

The query adds the summary row at the bottom.

select *

from ( select Blank,

ProviderId,

RecipientId,

Indicator,

Date

from XXXExport

union all

select ' ', rtrim(cast((select count(*) from XXXExport) as varchar)), 'rows', '', '') a

order by indicator desc

thanks for your help.