Exporting to a text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Exporting to a text file

Author

Topic

vini
Starting Member

11 Posts

Posted - 2006-11-30 : 08:47:13

Hiya all,

I am trying to export from a header and a detail table to a textfile with the following format,

HEADER 1
DETAIL 1
DETAIL 1
DETAIL 1
HEADER 2
DETAIL 2
DETAIL 2
DETAIL 2
etc
etc

EDIT: I Include the sql if it helps

SELECT --HEADER
'HHF' AS Field1,
'0000000000' AS SerialNo,
'1' AS TransStatus,
W.WH_Code,
(CASE WHEN DT.DType_Code = 'U' OR
DT.DType_Code = 'L'
THEN DT.DType_Code
ELSE ''
END) AS ToStoreCode,
'1' AS DocSubType,
'' AS AccountCode,
(DT.DType_Code + S.SM_Code + RIGHT(D.DOC_Code,6)) AS DOC_Reference,
(SELECT SUBSTRING(CONVERT(varchar,D.DOC_date,120),9,2) +
SUBSTRING(CONVERT(varchar,D.DOC_date,120),6,2) +
SUBSTRING(CONVERT(varchar,D.DOC_date,120),1,4)) AS DOC_Date,

(CASE WHEN DT.DType_Code = 'SI' OR
DT.DType_Code = 'SH' OR
DT.DType_Code = 'SF' OR
DT.DType_Code = 'SC' OR
DT.DType_Code = 'TT' OR
DT.DType_Code = 'TF' OR
DT.DType_Code = 'AD' OR
DT.DType_Code = 'JI'
THEN DT.DType_Code
ELSE ''
END) AS DocumentType,
'' AS PriceCode,
D.DOC_CashFlag AS CashWeeklyFlag,
D.DOC_PaidFlag AS CashPaidFlag,
(SELECT SUBSTRING(CONVERT(varchar,getdate(),120),9,2) +
SUBSTRING(CONVERT(varchar,getdate(),120),6,2) +
SUBSTRING(CONVERT(varchar,getdate(),120),1,4)) AS REF_Date

FROM tblDocuments AS D INNER JOIN tblWarehouse AS W ON D.DOC_WhId = W.WH_Id
INNER JOIN tblDocumentsTypes AS DT ON D.DOC_TypeId = DT.DTYPE_Id
INNER JOIN tblSalesman AS S ON D.DOC_SmId = S.SM_Id

SELECT --DETAIL
'HHL' AS Field1,
'0000000000' AS SerialNo,
PROD_Code AS ItemCode,
DTL_ProdQuantity AS ItemQty,
DTL_ProdAmount AS ItemGrossValue,
DTL_ProdDiscAmount AS ItemDiscAmount,
'0000000.00' AS ItemOverallDisc,
DTL_ProdVatAmount AS ItemVatValue,
'0000000.00' AS ItemDiscVatVal,
PVT_Code AS ItemVatCode,
(DTL_ProdAmount - DTL_ProdDiscAmount) AS ItemSalePrice,
DTL_DiscPerc0 AS ItemDiscRate,
'xxxxxx' AS ItemVatRate,
DTL_ProdUnitPrice AS ItemUnitPrice
FROM tblDocumentDetails INNER JOIN tblProducts ON DTL_ProdId=PROD_Id
INNER JOIN tblPvatTypes ON PROD_PvtId = PVT_Id

Anyone got any ideas?

Thanks in advance,
Vini

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-11-30 : 08:51:35

Yes.
For this kinda thing you can use Reporting services.

Create a report - with the relevant breaks (for the headers) followed by all the dtail rows.
Then render it in the format you like (It has a couple of options) eg csv, excel, etc....

Duane.

vini
Starting Member

11 Posts

Posted - 2006-11-30 : 08:53:09

appreciate the reply ditch but i am supposed to only use dts
:(

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-30 : 12:46:30

It's not DTS, but i don't think you can do this in DTS.
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

vini
Starting Member

11 Posts

Posted - 2006-12-01 : 03:45:37

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources