Post reply

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

  • 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

  • February 27, 2004 at 12:19 pm

    #496271

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

     


    * Noel

  • February 27, 2004 at 12:25 pm

    #496273

    Do you know where ican find some examples of it?

  • February 27, 2004 at 12:33 pm

    #496278

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


    * Noel

  • February 27, 2004 at 3:11 pm

    #496309

    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.

  • February 27, 2004 at 3:16 pm

    #496311

    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

  • February 27, 2004 at 3:21 pm

    #496312

    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]

  • August 10, 2004 at 10:01 am

    #518347

    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

     

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

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