Adding Timestamp and Date to a DTS textfile

[abienz]

Hi there,

I need to set up a way of creating a csv file in SQL Server 7 and at the start of this file I would like a timestamp of when the file was last created and then at the end of the file I would like a count of the total amount of records included in the file.

I can manage to create a csv file in SQl Server by right clicking my table and going to all tasks, export data, then choosing my source and detination, no problem, but I haven't noticed a default function to add these and I'm not too sure about how to edit the SQL to do it myself.

Has anyone any ideas?

Cheers,
Al.

 

[RickCole]

The following SQL in your export should do the job.

Select getdate()as Create_Date, a.*, Rec_Count = (select count(*) from table_name) from table_name a

Where tablename is the name of your table.

You wil how ever have the same create date and record count repeated for every record in your table.

Rick.

 

[abienz]

Yeah that's the problem, I really would just like it to be so that I only have the date written once at the start of the file, and the count written once at the end of the file

anymore ideas?

 

[RickCole]

You could work on something like the following and putting in your column names as required. This would leave the date and the count at the beginning of the file but If you added a record type then solution 2 might be worth looking at:

Solution 1

select convert(varchar(12),getdate())as Col_0, Null as Col_1, null as Col_2, null as Col_3
union
select convert(varchar(12),' '),col_name1, col_name2, col_name3 from tablename
union
select Col_1 = convert(varchar(12),(select count(*) from call_type)),null, null, null
order by col_1

Solution 2

select 1 as rec_type,convert(varchar(12),getdate())as Col_0, Null as Col_1, null as Col_2, null as Col_3
union
select 2 as rec_type, convert(varchar(12),' 'col_name1, col_name2, col_name3 from tablename
union
select 3 as rec_type, Col_1 = convert(varchar(12),(select count(*) from call_type)),null, null, null
order by rec_type


Rick.