I'm exporting data from SQL to a CSV file. But I need to include some metadata as the first line in the CSV file and I'm not sure how.
Ultimately I'm trying to get this outcome: "[email protected],[email protected]" ITN_USER,SITE_ID,TICKET_NUMBER,VALIDATING_CARRIER_CODE,TICKET_ EXPIRATION_DATE,TICKET_CURR_CODE,RESIDUAL_TOTAL_AMT,TICKET_TOT AL_FARE,PASSENGER_NAME,FIRST_ORIG_APT_CODE,FIRST_DEST_APT_CODE ,FIRST_DEPART_DATE,TICKET_ISSUE_DATE,CRS_LOCATOR,TICKET_STATUS _ID,TICKET_TYPE,RSVN_SYS_ID,TICKETING_LOCATION,TICKET_BASE_FAR E,TICKET_TAX,FARE_CALC_LINE vbear,abccorpus,0017845439769,AA,08MAY2009,USD,1226.57,1629.00 ,bear/jack,MSY,ORD,17MAY2008,08MAY2008,,,electronic,,,,, josh,gmail,0167846739059,UA,19JUN2009,USD,354.00,354.00, smith/john,LAX,PDX,25JUN2008,19JUN2008,,,,,,,, dgarcia,abccorpmx,1327959759566,MX,03AUG2009,MXN,6828.06,6828. 06,smith/jane,MEX,GUA,07AUG2008,03AUG2008,,,electronic,,,,,
The part I'm missing is "[email protected],[email protected]"
Here is my current code:
CREATE VIEW UnusedTicketsExport AS
SELECT 'ITN_USER' as ITN_USER, 'SITE_ID' as SITE_ID, 'TICKET_NUMBER' as TICKET_NUMBER, 'VALIDATING_CARRIER_CODE' as VALIDATING_CARRIER_CODE, 'TICKET_EXPIRATION_DATE' as TICKET_EXPIRATION_DATE, 'TICKET_CURR_CODE' as TICKET_CURR_CODE, 'RESIDUAL_TOTAL_AMT' as RESIDUAL_TOTAL_AMT, 'TICKET_TOTAL_FARE' as TICKET_TOTAL_FARE, 'PASSENGER_NAME' as PASSENGER_NAME, 'FIRST_ORIG_APT_CODE' as FIRST_ORIG_APT_CODE, 'FIRST_DEST_APT_CODE' as FIRST_DEST_APT_CODE, 'FIRST_DEPART_DATE' as FIRST_DEPART_DATE, 'TICKET_ISSUE_DATE' as TICKET_ISSUE_DATE, 'CRS_LOCATOR' as CRS_LOCATOR, 'TICKET_STATUS_ID' as TICKET_STATUS_ID, 'TICKET_TYPE' as TICKET_TYPE, 'RSVN_SYS_ID' as RSVN_SYS_ID, 'TICKETING_LOCATION' as TICKETING_LOCATION, 'TICKET_BASE_FARE' as TICKET_BASE_FARE, 'TICKET_TAX, FARE_CALC_LINE' as TICKET_TAX, 'FARE_CALC_LINE' as FARE_CALC_LINE -- Header
UNION ALL
SELECT Cast(ITN_USER as CHAR(40)) ITN_USER, Cast(SITE_ID as CHAR(100)) SITE_ID, Cast(TICKET_NUMBER as VARCHAR(25)) TICKET_NUMBER, Cast(VALIDATING_CARRIER_CODE as CHAR(2)) VALIDATING_CARRIER_CODE, REPLACE(CONVERT(VARCHAR(11), TICKET_EXPIRATION_DATE, 106), ' ', '') TICKET_EXPIRATION_DATE, Cast(TICKET_CURR_CODE as CHAR(3)) TICKET_CURR_CODE, Cast(RESIDUAL_TOTAL_AMT as CHAR(8)) RESIDUAL_TOTAL_AMT, Cast(TICKET_TOTAL_FARE as CHAR(8)) TICKET_TOTAL_FARE, Cast(PASSENGER_NAME as VARCHAR(29)) PASSENGER_NAME, Cast(FIRST_ORIG_APT_CODE as CHAR(3)) FIRST_ORIG_APT_CODE, Cast(FIRST_DEST_APT_CODE as CHAR(3))FIRST_DEST_APT_CODE, REPLACE(CONVERT(VARCHAR(11), FIRST_DEPART_DATE, 106), ' ', '') FIRST_DEPART_DATE, REPLACE(CONVERT(VARCHAR(11), FIRST_DEPART_DATE, 106), ' ', '') TICKET_ISSUE_DATE, Cast(CRS_LOCATOR as VARCHAR(6))CRS_LOCATOR, Cast(TICKET_STATUS_ID as CHAR(1))TICKET_STATUS_ID, Cast(TICKET_TYPE as VARCHAR(10))TICKET_TYPE, Cast(RSVN_SYS_ID as VARCHAR(10))RSVN_SYS_ID, Cast(TICKETING_LOCATION as VARCHAR(9)) TICKETING_LOCATION, Cast(TICKET_BASE_FARE as CHAR(8)) TICKET_BASE_FARE, Cast(TICKET_TAX as CHAR(100)) TICKET_TAX, Cast(FARE_CALC_LINE as CHAR(244)) FARE_CALC_LINE FROM UnusedTickets WHERE INSERT_DATE = '01-31-2018'
EXEC xp_cmdshell 'bcp "SELECT * FROM GDSX.dbo.UnusedTicketsExport" queryout "C:\Users\Public\Documents\ciazumano_unusedticket_20180131.csv" /c /t, -T'
DROP VIEW UnusedTicketsExport
I appreciate any help or suggestions that can be provided. Thanks!
Here is what I used to get this working:
CREATE VIEW UnusedTicketsExport AS
SELECT ' [email protected],[email protected]' as LINE -- Emails
UNION ALL
SELECT ' ITN_USER,SITE_ID,TICKET_NUMBER,VALIDATING_CARRIER_CODE,TICKET_EXPIRATION_DATE,TICKET_CURR_CODE,RESIDUAL_TOTAL_AMT,TICKET_TOTAL_FARE,PASSENGER_NAME,FIRST_ORIG_APT_CODE,FIRST_DEST_APT_CODE,FIRST_DEPART_DATE,TICKET_ISSUE_DATE,CRS_LOCATOR,TICKET_STATUS_ID,TICKET_TYPE,RSVN_SYS_ID,TICKETING_LOCATION,TICKET_BASE_FARE,TICKET_TAX,FARE_CALC_LINE ' as LINE -- Header
UNION ALL
SELECT Cast(ITN_USER as CHAR(40)) + ',' + Cast(SITE_ID as CHAR(100)) + ',' + Cast(TICKET_NUMBER as VARCHAR(25)) + ',' + Cast(VALIDATING_CARRIER_CODE as CHAR(2)) + ',' + REPLACE(CONVERT(VARCHAR(11), TICKET_EXPIRATION_DATE, 106), ' ', '') + ',' + Cast(TICKET_CURR_CODE as CHAR(3)) + ',' + Cast(RESIDUAL_TOTAL_AMT as CHAR(8)) + ',' + Cast(TICKET_TOTAL_FARE as CHAR(8)) + ',' + Cast(PASSENGER_NAME as VARCHAR(29)) + ',' + Cast(FIRST_ORIG_APT_CODE as CHAR(3)) + ',' + Cast(FIRST_DEST_APT_CODE as CHAR(3)) + ',' + REPLACE(CONVERT(VARCHAR(11), FIRST_DEPART_DATE, 106), ' ', '') + ',' + REPLACE(CONVERT(VARCHAR(11), TICKET_ISSUE_DATE, 106), ' ', '') + ',' + Cast(CRS_LOCATOR as VARCHAR(6)) + ',' + Cast(TICKET_STATUS_ID as CHAR(1)) + ',' + Cast(TICKET_TYPE as VARCHAR(10)) + ',' + Cast(RSVN_SYS_ID as VARCHAR(10)) + ',' + Cast(TICKETING_LOCATION as VARCHAR(9)) + ',' + Cast(TICKET_BASE_FARE as CHAR(8)) + ',' + Cast(TICKET_TAX as CHAR(100)) + ',' + Cast(FARE_CALC_LINE as CHAR(244)) as LINE