BCP Utility from Unpivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BCP Utility from Unpivot

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-20 : 11:35:08

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 12:43:09

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.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-20 : 13:36:56

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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-21 : 01:14:58

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources