Write to File Statement

This article document the result structure required to use the Import/Export system to export a query to a file.

Writing to a file requires two columns:

1. File Content
   The string of text to be placed into the file. Data can be XML, CSV, Tab-Delimited, etc...
    

2. File Path And Name
   The Directory and File Path of where the file is to be created.

Sample SELECT Command and output:

Within this example, the SELECT statement returns a list of Companies & Divisions in the database in an XML format. The results will be saved into a file named "ImportExport.txt" in the "\\Agrisoft-DV\ArImportFiles\" folder.

Sample Stored Procedure to Get Released EDI Orders

^{CREATE PROCEDURE spEDIGetReleasedOrders}

^AS

^{-- Create Temp Table}

^{CREATE TABLE #tmpEDISend(ERPRecordID int)}

^{-- Get Released Records}

^{INSERT INTO #tmpEDISend}

^{SELECT ERPRecordID} 

^{FROM dbo.vwEDI_TransmissionHeader}

^{WHERE TransMissionStatusID = 1}

^{AND DocumentTypeCode = '5000'}

^{AND OrderPartnerCode = 'CLIENT2';}

^{-- Get Details for XML File} 

SELECT (SELECT o.ordno as OrderNumber, cl.sname as CustomerNumber, o.orddt as PODate, o.shipdate as ShipDate, o.deldt as DeliveryDate, ol.itemcd as ItemNumber, ol.ordqty as OrderQty, ol.olPack as OrderPack, i.descr as ItemDescription

FROM custloc cl

INNER JOIN OrderMaster o ON o.clocseqno = cl.clocseqno

INNER JOIN #tmpEDISend t ON o.ordseqno = t.ERPRecordID

INNER JOIN OrderLine ol ON o.ordseqno = ol.ordseqno

INNER JOIN ItemMaster i ON i.ItemMasterID = ol.ItemMasterID

FOR XML PATH ('OrderLine'), ROOT ('OrderLines')) AS 'RESULT',

'C:\Docs\Orders.xml' AS 'FilePathAndName'

^{-- Mark Records as Transmitted}

^{INSERT INTO EDI_Transmission (ERPRecordID, SourceTypeID, ReleaseDate, DocumentTypeID, TransmissionStatusID, UserProfileID)}

^{SELECT ERPRecordID, 1, getDate(), 9, 3, 55}

^{FROM #tmpEDISend}

^{-- Get Rid of the Temp Table}

^{DROP TABLE #tmpEDISend}

^GO

Mapping Tables

The ImportExportMap table should have the following rows created for the parameters: