Exporting From One Table to Multiple Files

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

Exporting From One Table to Multiple Files

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-15 : 13:45:59

Guys I have data in sql table

Ex:

AcctNum AcctName Fname Lname transaction
123 test john doe 100
123 test john doe 150
345 test2 jack nick 50
345 test2 jack nick 300

What I need is export this data into files based on AcctNum.... if there are 5 distinct AccNum, there should be 5 flat files created with each file having info for its own acctnum. I am sure many of you have done this before... wanted to check if someone can help. Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 14:54:06

Years ago I have done something like this in SSIS.
Are you working with SSIS?

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-15 : 14:56:38

quote:
Originally posted by webfred

Years ago I have done something like this in SSIS.
Are you working with SSIS?

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

I was about to create a Proc but seems like SSIS will be must faster.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-15 : 15:06:02

I could have used conditional splitting, but cannot do with dynamic account number...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 15:40:49

I was testing without using SSIS.
Try this:

---- We need sample data to play with this
----declare @Sample table 
--create table Sample(AcctNum int, AcctName varchar(255), Fname varchar(255), Lname varchar(255), trsaction int)
--insert Sample
--select 123, 'test', 'john', 'doe', 100 union all
--select 123, 'test', 'john', 'doe', 150 union all
--select 345, 'test2', 'jack', 'nick', 50 union all
--select 345, 'test2', 'jack', 'nick', 300
--
--select * from Sample

-- here begins the solution
-- replace Sample with your tablename
-- replace Drive and Filename and so on like you need it.
set nocount on

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000),
		@AcctNum varchar(255),
		@i int
DECLARE		@keepDifferentAccts table (AcctNum int, IsDone char(1))

insert @keepDifferentAccts select Acctnum, 0 from Sample group by AcctNum

set @i=0
while @i <= (select count(*) from @keepDifferentAccts where IsDone = '0')
begin
	select top 1 @AcctNum=convert(varchar(255),AcctNum) from @keepDifferentAccts where IsDone=0
	
	SET @FileName = REPLACE('e:\Acct_'+@AcctNum+'_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
	SET @bcpCommand = 'bcp "SELECT * FROM Sample where AcctNum='+@AcctNum+'" queryout "'
	SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P password -c'
	
	EXEC master..xp_cmdshell @bcpCommand

	update @keepDifferentAccts set IsDone='1' where AcctNum=convert(int,@Acctnum)
	set @i=@i+1
end

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-19 : 07:36:44

Thanks a lot Webfred... this will certainly work. I will give a try today.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-19 : 11:24:14

---select * From dbo.Bank_Data_Source

DECLARE @FileName varchar(500),
@bcpCommand varchar(2000),
@ACCT_NBR varchar(255),
@i int
DECLARE @keepDifferentAccts table (ACCT_NBR varchar(255), IsDone char(1))

insert @keepDifferentAccts select ACCT_NBR, 0 from Bank_Data_Source group by ACCT_NBR
--select * from @keepDifferentAccts
set @i=0
while @i <= (select count(*) from @keepDifferentAccts where IsDone = '0')
begin
select top 1 @ACCT_NBR= ACCT_NBR from @keepDifferentAccts where IsDone=0

SET @FileName = REPLACE('e:\\outfile\Acct_'+@ACCT_NBR+'_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM Dev_Adhoc.dbo.Bank_Data_Source where ACCT_NBR='+@ACCT_NBR+'" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'

EXEC master..xp_cmdshell @bcpCommand

update @keepDifferentAccts set IsDone='1' where ACCT_NBR= @ACCT_NBR
set @i=@i+1
end

-------------------------

I used the above code to create export-files based on account number... it did create some files; however, there was this error:

NULL
Starting copy...
SQLState = 22003, NativeError = 248
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The conversion of the varchar value '7986011679' overflowed an int column.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

All the fields in dbo.Bank_Data_Source are VARCHAR... I am not sure about -T -c... is that the issue here?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-19 : 14:12:25

What datatype is the Acct_Nbr in your 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-19 : 15:57:16

quote:
Originally posted by webfred

What datatype is the Acct_Nbr in your table?

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

It is varchar... I have kept all of the datatype as varchar.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-20 : 02:47:17

quote:
Originally posted by GhantaBro

quote:
Originally posted by webfred

What datatype is the Acct_Nbr in your table?

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

It is varchar... I have kept all of the datatype as varchar.

I was missing quotes around @acct_nbr and after using that I was able to export most of the files except for couple I got errors:

SQLState = 37000, NativeError = 102
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '5712326718'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 03:18:09

Can you show the actually used query?

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 : 04:03:32

quote:
Originally posted by webfred

Can you show the actually used query?

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

Hey Webfred thanks for your help... it works now.. the real issue was with the WHILE loop... it did not loop enough number of times. It is fixed now. The error I got however was due to wrong quotes when I was thinking the issue is with int casting...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 04:14:52

fine

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