Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unhappy Unanswered: Unloading data into a file

    In SQL Server 2000,
    how can I export the data from a table into a file ?

    For example, I selected data into a temporary table named #Temp :

    SELECT ID + CONVERT(varchar,GetDate(),103),
    RTrim(LastName) + ' ' + RTrim(FirstName)
    INTO #Temp
    FROM PERSONS;

    And then I want to unload data from the table #Temp to a chosen file.

    Is it possible to do without using DTS ?

    Thx

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    should be able to use bcp - well documented in BOL
    Regards
    Dbabren

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Can I execute an "out Bcp" in the Query Analyser ?

    And will I be able to define the columns delimiter ?

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    I don't believe bcp will run from inside QA.
    Regards
    Dbabren

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Karolyn
    Can I execute an "out Bcp" in the Query Analyser ?

    And will I be able to define the columns delimiter ?
    Yes, you will....

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    IF...

    my statement :
    -----------------
    Select COLUMN1 + COLUMN2 + Calculated date + Table name + Program version as Title,
    *,
    '$' as EndOfLine
    Into #TABLETEMP
    From TABLE1
    Where TABLE1.DTMAJL > (Select TABLE2.DTLOTVAL From TABLE2)

    my row delimiter :
    ---------------------
    @

    my file name :
    -----------------
    UnloadTABLE1.txt

    Then to unload #TABLETEMP with bcp, what would be the exact synthax ?

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Karolyn
    IF...

    my statement :
    -----------------
    Select COLUMN1 + COLUMN2 + Calculated date + Table name + Program version as Title,
    *,
    '$' as EndOfLine
    Into #TABLETEMP
    From TABLE1
    Where TABLE1.DTMAJL > (Select TABLE2.DTLOTVAL From TABLE2)

    my row delimiter :
    ---------------------
    @

    my file name :
    -----------------
    UnloadTABLE1.txt

    Then to unload #TABLETEMP with bcp, what would be the exact synthax ?
    BOL:

    When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:

    bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -Ppassword

    -t field_term

    Specifies the field terminator. The default is \t (tab character). Use this parameter to override the default field terminator.

    See BOL for details.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •