sqlsql-servert-sql

SQL Job to Pipe Delimited


I am running SQL Job (SQL Server 20208 R2) and I want the results of the script to save to a folder as pipe delimited. Is there any way of doing that by adding to the script? Below is the script. If not can I do it any other way?

SELECT Distinct 
    (SELECT REPLACE(CONVERT(VARCHAR(10),PatientDemographics.DateofBirth,101),'/','')) as DOB 
    , (SELECT REPLACE(CONVERT(VARCHAR(10),ScheduleEntry.ScheduleDate,101),'/','')) as DateofService
    , RTRIM(LTRIM(Insurances.EligibilityPayorNumber)) as InsurancePayorCode
    , RTRIM(LTRIM(ContractFacilityProviders.NPI)) as ProviderID
    , RTRIM(LTRIM(PatientInsuranceProfiles.Insurance1PolicyNumber)) as SubscriberInsuranceID
    , RTRIM(LTRIM(PatientInsuranceProfiles.Insurance1PolicyGroupNumber)) as SubscriberGroupNumber
    , RTRIM(LTRIM(PatientDemographics.firstname)) as SubscriberFirstName
    , RTRIM(LTRIM(PatientDemographics.MiddleInitial)) as SubscriberMiddleInitial
    , RTRIM(LTRIM(PatientDemographics.Lastname)) as SubscriberLastName
FROM 
    ScheduleEntry
LEFT JOIN 
    PatientDemographics ON ScheduleEntry.PatientAccount = PatientDemographics.AccountNumber
LEFT JOIN 
    Providers ON ScheduleEntry.ResourceCode = Providers.MedStarProviderIdentifier
LEFT JOIN 
    Facilities ON ScheduleEntry.FacilityCode = Facilities.MedStarFacilityIdentifier
LEFT JOIN 
    AddedResource ON ScheduleEntry.ResourceCode = AddedResource.AddedResourceCode
LEFT JOIN 
    Caregiver ON ScheduleEntry.ResourceCode = Caregiver.CaregiverCode
LEFT JOIN 
    PatientInsuranceProfiles ON ScheduleEntry.PatientAccount = PatientInsuranceProfiles.PatientAccountNumber
LEFT JOIN 
    Insurances ON PatientInsuranceProfiles.Insurance1Mnemonic = Insurances.Mnemonic
LEFT JOIN 
    ContractFacilityProviders ON PatientDemographics.PrimaryPhysician = ContractFacilityProviders.ProviderIdentifier
WHERE 
    ScheduleEntry.ScheduleDate >= getdate() 
    AND ScheduleEntry.ScheduleDate <= getDate() + .50
    AND PatientInsuranceProfiles.Insurance1ContractIdentifier = ContractFacilityProviders.ContractIdentifier
    AND PatientinsuranceProfiles.ActiveFlag = 1
    AND EligibilityPayorNumber > 1
    AND ContractFacilityProviders.NPI > 1
ORDER BY
    SubscriberLastName

Solution

  • Several ideas

    1) Write an SSIS package to export to a flat file, using the pipe as the delimiter. 2) Use BCP as stated already 3) Use sqlcmd to output the result

    SSIS will be a nice solution for a SQL job.

    I would also echo the previous posters who suggested checking that your aren't negating the effects of your left joins by using the tables in the where clause