DTS - Export to a text file with mixed records

[Guest_imported]

Hi,

I want to export data to a text file but there's 2 kind of record.

For example I want to put a same text file, information about a driver and about its cars: if in the first column there is a '1' it's driver data, if it is a '2' it's car data. In a concrete way:

1 Mr Jim Smith NewYork
2 Bmw 1988
2 Audi 1995
1 Mme Claudia Foo Chicago
2 Gmc 2000
...

Someone knows how to deal with that ?
Thank you

Vincent

 

[tlbroadbent]

Please provide more information.[ul][li]Are the two record types in the same table?
[li]Do you want the output to look like your example?[/ul]If the answer to these two questions is yes then just export the table to the text file. There is no special need.

If the answer to the first question is 'No' you can create a view to merge the tables and then export using the View as the source.

If the answer to the second question is is 'No' then we need to know the requirement before suggesting a solution. Most likely, the solution will require a View or Stored Procedure. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Guest_imported]

Ok,

First, the requirements are:
- I export with Dts (Sql Server 7) from a Sql Server Database to a single "fixed fields" text file.
- the length of the columns are not the same in the 2 types of records

The records are not in the same table and are the result of queries. You said I can create a view but how can I write one with a serie of 2 kind of records as a result?

Thanks

Vincent
I am french so excuse my english ...

 

[tlbroadbent]

You can use CONVERT or CAST to make the columns the same length and data type.

Create View vCombineInfo AS
SELECT ID=Cast(ID As Char(6)), CAST(DriverInfo As Char(80))
UNION
SELECT Cast(ID As Char(6)), CAST(CarInfo As Char(80))
Go

Use the View as the source in DTS or instead of creating a View, use a query as the DTS source and insert the Union query in the DTS query screen. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Guest_imported]

I think there is a misunderstanding:

-> The 2 kind of records have a different number of fields and these fields have different length. And I need to keep these different length because the process that will be applied to the text file deal with lengths.

Vincent

 

[tlbroadbent]

Because you are exporting to a text file, you can still use CAST and a View to merge 2 differing record sets.[ul]Create View vCombineRecords As

Select ID, ExportInfo=Cast(Cast(col1 As char(??)) + cast(col2 As char(??)) + Cast(col3 As char(??) + ... As char(max chars))
From DriverTable
Union
Select ID, cast(Cast(col1 As char(??)) + cast(col2 As char(??)) + Cast(col3 As char(??) + ... As char(max chars))
From CarTable[/ul]This will yield a View with two columns, ID and ExportInfo. ExportInfo will be of character data type and 'max chars' long. Each of the individual columns wil be the length you specify which should be the maximum column length. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Guest_imported]

Great Idea, thank you but I still have a problem ... when I write the following test:

SELECT Info = Cast(Cast(llastname AS varchar(35))
+ Cast(lfirstname AS varchar(30)) AS varchar(65))
FROM CONTACT

In the 'Info' column the result is for example 'SmithJohn' without the spaces between the 2 data. Any Idea ?

More, as I have sent a message sooner (another thread), what happens if the &quot;llastname&quot; is <NULL> or ''? the result is '[35 spaces]John' ? or 'John' ?

Thanks

Vincent

 

[tlbroadbent]

SQL provides a number of String Functions, all documented in SQL BOL.

[/b]Add a space between columns using the concatenation orperator '+'.[/b]

SELECT Info = Cast(Cast(llastname AS varchar(35)) + ' '
+ Cast(lfirstname AS varchar(30)) AS varchar(65))
FROM CONTACT

Trim spaces with LTRIM and RTRIM

SELECT Info = Cast(Cast(ltrim(rtrim(llastname)) AS varchar(35)) + ' '
+ Cast(ltrim(rtrim(lfirstname)) AS varchar(30)) AS varchar(65))
FROM CONTACT

Check for null with the isnull function

SELECT Info = Cast(Cast(ltrim(rtrim(isnull(llastname,''))) AS varchar(35)) + ' '
+ Cast(ltrim(rtrim(isnull(lfirstname,''))) AS varchar(30)) AS varchar(65))
FROM CONTACT
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Guest_imported]

Thank you again !!

Vincent