Export 3 Data Sources - Best Practices and Procedures

[eb24]

I need to export some data from a SQL Server using DTS to a .TXT file. I wanted some advice to see what others are doing and to see what is the best way to go about it.

The Text File should have this format:

1000, Hello, Test Run, Start
2000, John Doe, 12345, 123 Drive Way, Los Angeles, CA
2000, Sarah Jones, 49493, 495 Brick Drive, Ithaca, NY
2000, ... 2000 level data
.
.
.
2000, ... 2000 level data
3000, John Doe, 112233445566, 20041104, 20041231
3000, Sarah Jones, 302392023231, 20041030, 20050402
3000, ... 3000 level data
.
.
.
3000, ... 3000 level data

If one will notice, the file contains 3 types of data, i.e. 1000 being a simple 1 line header, 2000 level data being employee information, and 3000 level data being credit card information.

My question is, I will be using 3 insert/select statements in my DTS job and export this data to a .txt file by merging the 3 types data, correct? Or are there any tips/techniques you all can give me?

Appreciate any help you can send my way.

 

[mrdenny]

You will be using 3 queries to do this. You can merge them all together using a union all statement. Something like this should fit.

select 1000, Hello, Test Run, Start, '', ''
union all
select 2000, Name, convert(varchar(10), id), address1, city, State
from table1
union all
select 3000, Name, convert(varchar(20), ccid), convert(varchar(10), date1, 101), convert(varchar(10), date2, 101), ''
from table2

You'll notice the extra column in the table2 select. This is because all select statements within a union must have the same number of fields.

This data will be very hard to use in the future. I would recommend exporting this data into 3 seperate files and using it that way.

Denny

--Anything is possible. All it takes is a little research. (Me)