Export column from SQL to .txt format

Question

Export column from SQL to .txt format

SQL Severe DBA

Hall of Fame

Points: 3579
More actions
May 21, 2014 at 10:43 am

#308198

I have table1 with col1 varchar,col2 int , col3 xml , col4 bit
Can any one suggest a best way to fetch the col3 into file (.txt or .sql) into seprate file for each col3 record . I want to export this in different files for each record if possible with date and time stamp .
Its a tricky one for me .

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

RantaSanta (5/21/2014)
I have table1 with col1 varchar,col2 int , col3 xml , col4 bit
Can any one suggest a best way to fetch the col3 into file (.txt or .sql) into seprate file for each col3 record . I want to export this in different files for each record if possible with date and time stamp .
Its a tricky one for me .

SSIS

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

There might not be any need to go to SSIS.

If you have some kind of primary key, you could use it to generate all the files with a cursor.

DECLARE @id nvarchar(9)

DECLARE @bcp nvarchar(MAX)

DECLARE Test CURSOR READ_ONLY FAST_FORWARD

FOR

SELECT idColumn

FROM Table1

OPEN Test

FETCH NEXT FROM Test INTO @id

WHILE @@FETCH_STATUS = 0

BEGIN

SET @bcp = 'bcp "select col3 FROM Table1 WHERE idColumn = ' + @id + '" queryout ' +

'"C:\Somelocation\Somename' + REPLACE( REPLACE( REPLACE( CONVERT( char(23), GETDATE(), 126), ':', ''), '-', ''), '.', '') + '.txt"'

PRINT @bcp

--EXECUTE xp_cmdshell @bcp

WAITFOR DELAY '00:00:00.004'

FETCH NEXT FROM Test INTO @id

END

CLOSE Test

DEALLOCATE Test

There might be some errors, but I you'll have to test the code and try to figure out how to correct them (that's how you'll learn more ;-)).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 3

Thanks Luis ,

However , I realized extracting xml column to .txt/.sql I lose the formatting even If I extract in .xml .:crazy:

For now I am going to explore the Powershell to accomplish this . Not sure where to start but will share if I am able to .

But the same code which you shared will work for any other format type with out any problem .

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

RantaSanta (5/23/2014)
However , I realized extracting xml column to .txt/.sql I lose the formatting even If I extract in .xml .:crazy:

Actually, you won't. It only looks that way in the grid mode.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Maybe you didn't play enough with bcp. This article might help you.http://sqlsouth.wordpress.com/2014/05/23/export-xml-from-sql-server-using-bcp/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2