July 20, 2010 at 9:35 am
Hi Guys,
I am trying to export data into text files based on the account number... the query runs fine, but when I use Pivot and Unpivot to export data in vertical, the export doesn't work and it doesnt fail either... What I am doing is based on Account Number, I am creating separate files and the data has to be exported vertically. It is running correctly with simple horizontal select... BCP doesn't like pivot/unpivot seems like... any experience with such issue?
--Sample
--I had to create table with same datatype and size for pivot
Create table Bank_Data_Source(
captr_dt varchar(50) ,acct_nbr varchar(50),amt varchar(50),serial_nbr varchar(50), dr_cr_cd varchar(50), [desc] varchar(50), eor varchar(50)
)
Insert into Bank_Data_Source
Select '7/12/2008', '909878789', '20.50', '1223', 'C', 'Bank deposit', '^'
union all
Select '7/14/2008', '909878789', '20.50', '1223', 'C', 'Bank deposit', '^'
union all
Select '7/12/2008', '888988788', '200.50', '0', 'C', '', '^'
union all
Select '7/14/2008', '888988788', '120.50', '234', 'D', 'paid to dr...', '^'
DECLARE @FileName varchar(500),
@bcpCommand varchar(4000),
@ACCT_NBR varchar(255),
@i int
DECLARE @keepDifferentAccts table (ACCT_NBR varchar(255), IsDone char(1))
insert @keepDifferentAccts select cast(ACCT_NBR as varchar(255)) , 0 from Bank_Data_Source group by ACCT_NBR
--select * from @keepDifferentAccts
select @i = count(*) from @keepDifferentAccts where IsDone = '0'
while @i > 0
begin
select top 1 @ACCT_NBR= cast(ACCT_NBR as varchar) from @keepDifferentAccts where IsDone=0
SET @FileName = REPLACE('e:\Acct_' + @ACCT_NBR+ '_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','_')
SET @bcpCommand = ' bcp "select colVal from
(select' + '''D''' + ' + cast(ltrim(rtrim(captr_dt)) as varchar) captr_dt,' + '''N''' + ' + cast(ltrim(rtrim(acct_nbr)) as varchar) as acct_nbr ,' + '''T'''
+ ' + cast(case when LTRIM(RTRIM(dr_cr_cd)) = ' + '''D''' + ' then ' + '''-''' + ' + CAST(AMT as varchar) else ltrim(rtrim(AMT)) end as varchar) as amt, '+
'''N''' + ' + cast(LTRIM(rtrim(SERIAL_NBR)) as varchar) as serial_nbr, ' + '''L''' + ' + cast(LTRIM(rtrim(dr_cr_cd)) as varchar) as dr_cr_cd, ' + '''P'''
+ ' + CAST( LTRIM(rtrim([Desc])) as varchar) as [desc], ' + '''''' + ' + CAST(ltrim(rtrim(EOR)) as varchar) as eor from Bank_Data_Source
where acct_nbr = ' + '''' + @ACCT_NBR + '''' + ') pivot_table
unpivot
( ColVal for Rows in (captr_dt ,acct_nbr ,amt ,serial_nbr, dr_cr_cd, [desc], eor )) pivot_handle' +'" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'
select @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
update @keepDifferentAccts set IsDone='1' where ACCT_NBR = @ACCT_NBR
set @i=@i - 1
end
July 20, 2010 at 11:38 am
Nevermind guys.. this is fine now... I used an interim table to insert record in and then used that table to export. It worked fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply