Syntax Temp Table in SP to Tab Delimited .txt file
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
)
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Ow sorry, hadn't noticed that when I read your question. In that case you should still use the Stored Procedure. Add a second statement to the SP to fetch the records (select * from #you_table).
Then use "exec your_sp" as SQL command in the OLE DB Source.
Then use "exec your_sp" as SQL command in the OLE DB Source.
ASKER
At the end of my SP I'm assuming?
correct, see snippet :-)
ALTER PROCEDURE [dbo].[P_StrategicAR] AS
BEGIN
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
)
select * from #StrategicAR
END
ASKER
It still will not let me finish my package. The file destination step says there are no input columns and that it needs at least one mappings between columns must be defined, but I can't obviously b/c I only have a temp table. Any work arounds?
Actually, now that I think about it, why use a temporary table? You can return the results directly from the first query instead. Just remove the "into #StrategicAR" line. In this case you don't need the second select statement.
ASKER
I have to have a temporary table, this is built on a customer side database and I have no choice in the matter. That's why I really wanted to do this within the SP.
Now you've lost me I'm afraid... The point of using a temporary table is if you need to get access to the same data later on. In your case you're using a local temp table, which means it gets deleted as soon as the SP ends. So really don't see what benefit you're getting by using it?
ASKER
Right, so if the write to tab delimited .txt file was in the SP then it would all be done at once without having to have the table on the customer site.
ASKER
I opened another question to do the way I want, but gave you points b/c this is a way to do it.
ASKER