title: SQL Export Data from a Query to Use Tilde (~)
body: <p>I have a basic Select query which brings me back a set of results (roughly around 100,000 records) which I currently have to export to .CSV Format , issue is I then have to remove the commas out of the results and replace it with a (~). Which I do using a file format application that I got off the web. </p>
  
  <p>But I'm trying to go about making this automated (if possible) to save time. Like e.g run off a stored procedure that can do this for me export the file in a (~) format. </p>
  
  <p>Does anyone have any tips how this stored procedure can be written or an pointers would be appreciated.</p>
  
  <p>p.s I have tried to use the export wizard but it just just crashes due to too many records.</p>
  
  <pre><code>Expected Result
  
   Test1~Test2~Test3
     5~6~7
  </code></pre>
  
  <p>(sql Script which I am running)</p>
  
  <pre><code> select 
  'SPK' as [AGENCY_CODE], -- should be set to SPK
  'OBCALL' as [MEDIA_CODE], -- should be set to OBCALL
  isnull(c.salutation,'') as [TITLE],
  isnull(c.otherName,'') as [FORENAME],
  isnull(c.name,'') as [SURNAME],
  isnull(c.attTXT64,'') as [STANDARDISED_NAME],
  replace(isnull(c.addr1, ''), ',', '.') AS [BEST_ADDRESS_LINE_1],
  replace(isnull(c.addr2, ''), ',', '.') AS [BEST_ADDRESS_LINE_2],
  replace(isnull(c.addr3, ''), ',', '.') AS [BEST_ADDRESS_LINE_3],
  replace(isnull(c.addr4, ''), ',', '.') AS [BEST_ADDRESS_LINE_4],
  isnull(c.postCode,'') as [BEST_POSTCODE],
  --'0' + isnull(c.phone1,'') as [TELEPHONE_N2O],-- should be populated with the spare field ORIG_TEL .
  RIGHT('0' + CONVERT(VARCHAR(11), c.phone1), 11) as [TELEPHONE_NO],-- should be populated with the spare field ORIG_TEL .
  convert(varchar(100),c.attDT03,120) as [DATE_TIMESTAMP],
  isnull(c.attTXT10,'') as [SM_CONTACT_KEY],
  isnull(c.attTXT89,'') as [ SM_ADDRESS_KEY], 
  isnull(c.attTXT11,'') as [ CAMPAIGN_IDENTIFIER],
  isnull(c.attTXT12,'') as [ WAVE_ID],
  isnull(c.attTXT13,'') as [OLDSTACK_NEWSTACK_FLAG],
  isnull(c.attTXT14,'') as [MARKET_3_FLAG],
  isnull(c.attTXT15,'') as [ADSL_2_FLAG],
  isnull(c.attTXT16,'') as [FIBRE_FLAG],
  isnull(c.attTXT17,'') as [LOAD_ID],
  isnull(c.attTXT18,'') as [CONTACT_POINT_KEY],
  isnull(c.attTXT19,'') as [DATA_POOL_URN],
  isnull(c.attTXT20,'') as [EVENT_KEY],
  isnull(c.attTXT21,'') as [BILLING_ACCOUNT_KEY] ,
  isnull(c.attTXT22,'') as [CAMPAIGN_SOURCE] ,
  isnull(c.attTXT23,'') as [CAMPAIGN_CODE] ,
  isnull(c.attTXT24,'') as [ CMT_ROLE_KEY],
  isnull(c.attTXT25,'') as [ CMT_LOCATION_KEY],
  isnull(c.attTXT26,'') as [BILL_ACCNT_NUM], 
  isnull(c.attTXT27,'') as  [BILLING_ACCOUNT_TYPE],
  --All other fields are as per the import record values
  --DATE/TIME_OF_CONTRACT to CONTRACT_END_DATE_SUPPLIER3
  
  CASE WHEN dx.datetime IS NULL THEN convert(varchar,getdate(),120) ELSE CONVERT(varchar, dx.datetime, 120) END
  as [DATE/TIMEOF CONTACT],
  
  Case when uc.campaignid = 3 then 'CT001' when uc.campaignid = 22 then 'CT001'  when uc.campaignid = 18 then 'CT011'  
       when uc.campaignid = 26 then 'CT013'
  end as [CAMPAIGN_TYPE], -- map to BT/DATA/10.CAMPAIGN_Code (return CAMPAIGN_TYPE)
  ISNULL((    CASE 
              WHEN dx.[Abandon] = 1 THEN 'OC039'
              ELSE d.code END),'OC042') 
   AS [OUTCOME_CODE], -- populate with (OC001-OCxxx)
  
  'TM' as [CHANNEL_MEDIA_CODE], --= (TBC)
  isnull(c.email,'') as [EMAIL_ADDRESS], -- populate with EMAIL_ADDRESS
  '' as [EMAIL_CONSENT], --= populate with EMAIL_CONSENT ***************
  '' as [INBOUND_TELEPHONE_NUMBER], --*****************
  '' as [COMPETITOR_SUPPLIER_1], -- CONT CONTRACT_END_DATE_SUPPLIER3 – poplulate ***********
  '' as [PRODUCT_FROM_SUPPLIER1], -- populate ************
  '' as [CONTRACT_START_DATE_SUPPLIER1], --*****
  '' as [CONTRACT_END_DATE_SUPPLIER1], --*****
  '' as [COMPETITOR_SUPPLIER2], --****
  '' as [PRODUCT_FROM_SUPPLIER2], --****
  '' as [CONTRACT_START_DATE_SUPPLIER2],-- *****
  '' as [CONTRACT_END_DATE_SUPPLIER2], --*****
  '' as [COMPETITOR_SUPPLIER3],-- ****
  '' as [PRODUCT_FROM_SUPPLIER3],--****
  '' as [CONTRACT_START_DATE_SUPPLIER3],-- *****
  '' as [CONTRACT_END_DATE_SUPPLIER3],-- *****
  
  --ORDER_NUM to NUMBER_OF_CALLS_MADE
  isnull(c.attTXT02,'') as [ORDER_NUM], -- Captured by Operator
  isnull(dx.duration,0) as [CALL_DURATION],
  -------------dxi.talk as [CALL_DURATION], -- populate difference start/end time (seconds)******
  isnull(c.attTXT09,'') as [WARMTH_RATING_NOW], -- captured by agent
  isnull(c.attTXT80,'') as [WARMTH_RATING_FUTURE_CAMPAIGNS], -- captured by agent
  isnull(dx.callid,'') [SOURCE_INTERACTION_ID],
  -------------isnull(a.id,'') as [SOURCE_INTERACTION_ID], -- Tpoints unique call Identifier --- Activity ID *******
  isnull(uc.callcount,'') as [NUMBER_OF_CALLS_MADE],
  
  (select left(ethnicOrigin, 1))  as [CALL_CONSENT_VALIDATE_FLG], -- ******** gift aid status - first byte only
  (select left(nationality, 5)) as [CALL_CONSENT_OUTCOME_CD], -- ******** nationality - first 5 bytes only
  
  --QUESTION _1 to ANSWER_3
  isnull(c.attTXT56,'') as [QUESTION_1], -- populate from SPARE_FIELD_28
  isnull(c.attTXT51,'') as [ANSWER_1], -- captured by the agent if SPARE_FIELD_1  populated
  isnull(c.attTXT29,'') as [QUESTION_2], -- poaddingpulate from SPARE_FIELD_28
  isnull(c.attTXT67,'') as [ANSWER_2], -- captured by the agent if SPARE_FIELD_2  populated
  isnull(c.attTXT37,'') as [QUESTION_3], -- populate from SPARE_FIELD_28
  isnull(c.attTXT58,'') as [ANSWER_3] -- captured by the agent if SPARE_FIELD_3  populated
  --isnull(c.attdt18,GETDATE()) as [Export_Date]
  
  from 
      u_contact c with (nolock)
  inner join u_campaigncontact uc with (nolock) on uc.contactid = c.id 
  inner join u_dispcode d with (nolock) on d.id = uc.resultcodeid
  outer apply (select top 1 duration, callid, 
  case when outcome = 113 then 1 else 0 end [Abandon], [datetime] from dxi_cdrlog cdr where cdr.urn = c.id order by callid desc) dx
   where 
       uc.campaignid in (3, 18, 22, 26) and d.dmc = 1 and c.created between DATEADD(week, -1, getdate()) AND  getdate()
  and c.importid &gt; 0
  </code></pre>