Results 1 to 6 of 6

Thread: Data import

  1. #1
    Join Date
    Mar 2004
    Location
    Zeist, The Netherlands
    Posts
    4

    Question Data import

    Hi,

    I do a monthly import in MSSQL 2K. The file contains 7.000.000 rows. These rows are loaded in to a special (preload) database. After the import several modifications are performed before it's uploaded to production.
    The importfile is a comma-seperated file, fields are enclosed by double-quotes (seems to be an issue for BCP?)

    If I'm right, there are 3 options to import:
    1) data transformation
    2) Bulk insert
    3) BCP import

    Could someone give me a more detailed explanation what the differences are between those methods? What's the fastest? disadvantages?

    Thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    1. DTS is more flexible, it can import/export from variety of sources (different RDBMS, Access, Excel, Text File, etc). You can also manipulate data during import/export.

    2. BULK INSERT can only import and you can't do any data massage. But it is the fastest of all.

    3. BCP can only work with text files and does not provide data manipulation.

    You have two choices.
    BULK INSERT and use stored procedure to manipulate data
    or
    Use DTS to import and manipulate data.

    The first one may be faster.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I prefer DTS because, it is more secure and manageable.

  4. #4
    Join Date
    Mar 2004
    Posts
    12
    I like DTS too, its features make it easier to create a data audit chain, which is the biggest plus to me.

    Besides in a processing window pinch you can always call bulkinsert from a DTS package by using an ExecuteSQL step.

  5. #5
    Join Date
    Mar 2004
    Location
    Zeist, The Netherlands
    Posts
    4

    BCP with fields surrounded by quotes

    small (stubpid?) secondary question: It seems BCP has no facility for defining "fields enlosed by", which is available using in data import. You can define how fields are separated (FIELDTERMINATOR), but that's not what I'm looking for.
    Am I missing something?

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Do you mean quoted identifier? If so, use -q switch in bcp.

Posting Permissions

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