How can i create a flat file (text file) ?

Question

Post reply

How can i create a flat file (text file) ?

Dan_2003

SSC Enthusiast

Points: 132
More actions
February 27, 2004 at 12:04 pm

#178229

Hi,
I would like to know how can i create a flat file from differents SQL Server tables. I created it in PL\Sql but now i should write it for T-SQL.
I created differents "temp" tables with the data that i have to work and now i would like to write some records in a flat file to transfer to another system.
Do you have an idea how can i do it?
Tia,
Dan

Viewing 8 posts - 1 through 7 (of 7 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

You have to use BCP or DTS but if you have connectivity with that other system may be you can use linked serves.

* Noel

Dan_2003 SSC Enthusiast Points: 132 More actions · Answer 2

Dan_2003

SSC Enthusiast

Points: 132

February 27, 2004 at 12:25 pm

#496273

Do you know where ican find some examples of it?

noeld SSC Guru Points: 96590 More actions · Answer 3

Take a look at this script that creates a stored procedure that uses BCP to export data to a file

* Noel

MikeMcDonald SSChasing Mays Points: 643 More actions · Answer 4

Another couple of ideas ... but certainly not the "nicest" way to do it, and definitely not suitable for anything but "smallish" tables.

1. Query Analyzer can have the output directed to a file (or do copy and paste from the output).

2. Use Microsoft Access as an intermediary ... create a new table, copying from the source table. Create a new linked table whixh is linked to the destination table. Copy and paste between the two Access tables.

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

Just saw your post Dan... here's a little snippet from some of the code I use. You'll have to fill in the @ vars, but it might be helpfull

Create Table #TempResults (Data varchar(8000))

set @sql = 'master.dbo.xp_cmdshell ''BCP ' + db_name(db_id()) + '..' + @TBName + ' OUT "' + @OutPutFile + '" -c -S"' + @@Servername + '" -a32768 -T -t"' + @Delimiter + '"'''

Insert Into #TempResults -- allows for checking of errors returns by xp_cmdshell

Exec (@SQL)

Once you understand the BITs, all the pieces come together

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Not that I know much about DTS, but I once tried this and it is really simple with DTS. If you need this on a regular basis like daily, schedule a job to run a package.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rehan SSC Enthusiast Points: 134 More actions · Answer 7

hi Noel

I am trying to use your script but i am little bit confused on it I appreciate if you will mention where to use the table name and the step to exec it.

thanks in advanced