troubleshooting Question

Syntax Temp Table in SP to Tab Delimited .txt file

Avatar of Nathan Riley
Nathan RileyFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
11 Comments1 Solution679 ViewsLast Modified:
Now I'm trying to get this into a tab delimited .txt file before the table drops in my SP.  I have learned how to do this with DTS packages and a normal table, but this SP will be on a 2005 SQL server and I'm unfamiliar with their packages.  I think it would be nice just to have it all in the SP.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
 
 
 
 
ALTER PROCEDURE [dbo].[P_StrategicAR] AS
 
select
AR.CusNo as PatientID,
PA.Last_Name as LastName,
PA.First_Name as FirstName,
ltrim(Substring(RP.Last_Name,0,40)) as RSPLastName,
ltrim(Substring(RP.First_Name,0,40)) as RSPFirstName,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PAddr1
else ltrim(Substring(RP.Addr1,0,40)) end as Address1,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PAddr2
else ltrim(Substring(RP.Addr2,0,40)) end as Address2,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PCity
else ltrim(Substring(RP.City,0,30)) end as City,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PState
else ltrim(Substring(RP.State,0,3)) end as State,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PZip
else ltrim(Substring(RP.Zip,0,12)) end as Zip,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PPhone
else ltrim(rtrim(RP.Phone)) end as Phone,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PEmp
else ltrim(rtrim(RP.PEmp)) end as Employer,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PEmpPh
else ltrim(rtrim(RP.PEmpPh)) end as EmployerPhone,
AR.DOCDT as InvoiceDate,
CL.Start_DOS as DOS,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.BirthDate
else ltrim(rtrim(RP.Birth_Date)) end as DOB,
case when ltrim(rtrim(RP.Last_Name)) is null then PA.PSSN
else ltrim(rtrim(RP.SSN)) end as SSN,
AR.BalancingTotalAmt as Amount,
AR.APLNO as InvoiceNo,
AR.Linenuma as LineItem,
CL.QtySh as ItemQty,
CL.Descr1 as ItemDescription,
AR.DOCTP as DocType,
AR.Branch as BranchName,
AR.INSNO as PrimaryInsurance
into #StrategicAR
from jur.Patmas PA
inner join jur.aropen ar
        on PA.cusno = ar.cusno and PA.patnum = ar.patnum --and ar.insno = rp.insno
full outer join jur.Clmlin CL
        on CL.Invno = AR.Aplno and CL.Linenuma = AR.Linenuma and CL.Cusno = AR.Cusno
left outer join jur.Repmas RP
        on PA.CusNo = RP.CusNo
where ar.insno = ''
and AR.APLNO not in
(
        select AR.APLNO
        from jur.AROPEN ar
        group by AR.APLNO
        having  sum(ar.BalancingTotalAmt) = 0
)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros