Fetching Data from 2 tables and send in text File using query

[chint...@gmail.com]

Hi friends need help for the following
I m using SQL 2000
What I want to do is I want to create a single Text File containg
data from 2 tables using DTS.
Basically my First Table is having 10 columns and second table is
having 40 columns so my text file will be generating of 50 columns in
that what I want to do is If suppose my first table has 5 rows and
second table has 20 rows so final text file will be having 25 rows in
all.
Now in text file first five rows should be of Table A which has 10
columns so from 11th column ownward it will have all null data.From
6th row ownward my second table is having data so in that case 1-10
columns will have null data and from 11th coulmn Table B data will be
there now I want to form a query in such a way that my result set
should be as mention above.

Sample
Table A
-------------------
Col A1 | Col A2 | Col A3
1 XYZ 10
2 MNO 20

Table B
-------------------
Col B1 | Col B2 | Col B3 | Col B4
1 x m 10
2 y n 20

Query Resultset
-------------------
Col A1 | Col A2 | Col A3 | Col B1 | Col B2 | Col B3 | Col B4
1 XYZ 10 null null null
null
2 MNO 20 null null null
null
null null null 1 x
m 10
null null null 2 y
n 20

So I will send this query in DTS to generate a Text file as shown in
resultset.
Please friend any one can suggest something..
Irrespective of relation I want to fetch data from both table and form
File so what can be the query to do so.

[Hugo Kornelis]

Hi chintu4uin,

The query to get this result set is as follows:

SELECT ColA1, ColA2, ColA3,
NULL AS ColB1, NULL AS ColB2, NULL AS ColB3
FROM TableA
UNION ALL
SELECT NULL AS ColA1, NULL AS ColA2, NULL AS ColA3,
ColB1, ColB2, ColB3
FROM TableB;

How to embed this in DTS is beyond me, for I never used DTS. But I
assume you can just call this query from DTS and get the results.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[chint...@gmail.com]

On Dec 8, 12:48 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:

Hi Hugo Kornelis, thnx for u r reply ur suggestion works.