SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 BCP Utility from Unpivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GhantaBro
Posting Yak Master

215 Posts

Posted - 07/20/2010 :  11:35:08  Show Profile  Reply with Quote
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? Thanks for taking time to help me on this.

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

Edited by - GhantaBro on 07/20/2010 11:42:01

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/20/2010 :  12:43:09  Show Profile  Visit webfred's Homepage  Reply with Quote
If your select is running fine but only if not used inside your BCP then you can insert the result of your query into an interim table and let your BCP have a simple select * from that table.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

GhantaBro
Posting Yak Master

215 Posts

Posted - 07/20/2010 :  13:36:56  Show Profile  Reply with Quote
quote:
Originally posted by webfred

If your select is running fine but only if not used inside your BCP then you can insert the result of your query into an interim table and let your BCP have a simple select * from that table.


No, you're never too old to Yak'n'Roll if you're too young to die.



Thanks Wbform that is exactly what I did. It works.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/21/2010 :  01:14:58  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000