SQL Server Performance

SQL 2000 Utilities: BCP and DTS

Discussion in 'SQL Server DTS-Related Questions' started by larryle, Sep 8, 2003.

  1. larryle New Member


    I am new to SQL 2000 Server, I would like to get help me with the following questions regarding SQL 2000 utility?

    1. I have attached the flat file#%92s data, which created in text format and I like to create a table in SQL 2000 database (Northwind database for example) based on the information of flat file#%92s data. Can you please take a look at a flat file and recommend of how to create a table with including table#%92s columns and datatype, etc…?

    2. If I want to Import the data from the flat file into a table, which created in above step in SQL Server database using the BCP utility. Can you please provide a command line of how to do that?

    3. If I want to Export that table to Excel using DTS utility, Can you please provide a command line of how to do that?


    Thanks in advance and I much appreciate for your help on this.

    Larry

    =======Here is a flat file's data that needs to Import into table in SQL 2000========

    1,Untitled,Mark Rothko,Oil,1961,5'9"x4'2";
    2,The Letter,Jan Vermeer,Oil,1666,1'5.25"x1'3.75";
    3,Four Apostles,Albrecht Durer,Oil,1526,7'1"x2'6";
    4,Big Self-Portrait,Chuck Close,Acrylic,1968,8'11"x6'11"x2;
    5,Three Angels,Andrei Rublyev,Tempura on wood,1410,4'8"x3'9";
    6,Voltaire,Jean-Antoine Houdon,Marble,1781,;
    7,Jaguar Devouring a Hare,Antoine-Louis Barye, Bronze,1851,1'4"x3'1";
    8,The Peacock Skirt,Aubrey Beardsley,Pen and Ink,1894,;
    9,Untitled Film Still #35,Cindy Sherman,Black-and-white photograph,1979,10"x8";
    10,Reclining Figure,Henry Moore,Elm wood,1939,3'1"x2'6";

    ========================================================================================

    larryle
  2. satya Moderator

    Refer to books online for DTSRUN utility to execute DTS functionality from command prompt.
    ANd using DTS wizard you can simply export the rows from Excel to SQL server which will create the table automatically and you can rename the same later.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. Twan New Member

    for number 2 you could also use bcp. The command line options are in Books OnLine under bcp utility|Overview

    something like:


    bcp database_name.owner.table_name in data_file -c -t, -S server_nameinstance_name -T
    also require -E if you're trying to insert into an identity column. NOTE bcp is not as easy to use as DTS, but it is liekly to be quicker for large files...

    for number 3 you could also initiate the process from within Excel, where you have the option to keep the link live so it is refreshed every time you open the spreadsheet. In Excel goto Data menu and choose Get External Data...

    Cheers
    Twan

Share This Page