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
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