Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Location
    Pnoenix, AZ
    Posts
    8

    Unanswered: Insert data from text file

    I have a comma delimited text file with about 500,000 records. I want to insert data into an existing table. The catch is that I want to commit 10,000 rows at a time. Can this be done via dts. There also is about 20 records in this file that have an error. I also need need a routine that will ignore bad records.

    Thank You!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Why 10,000 records at a time ? Have you looked at bcp ?

  3. #3
    Join Date
    Jan 2003
    Location
    Pnoenix, AZ
    Posts
    8
    Originally posted by rnealejr
    Why 10,000 records at a time ? Have you looked at bcp ?
    I do not have administrator access to the database. The log file is set to a limited size. The logfile will only let you committ a limited number of rows. I am not familiar with BCP. I am open to Any suggestions.

    Thanks

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Do you need to log this operation ?

  5. #5
    Join Date
    Jan 2003
    Location
    Pnoenix, AZ
    Posts
    8
    Originally posted by rnealejr
    Do you need to log this operation ?

    No I do not need to log the operation. A monthly text file is the backup for the data. If there is a problem I simply would reload the data from the text file.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Then I would use bcp or bulk insert. bcp is a command-line utility and bulk insert can be done using dts or transact sql. Let me know if you need an example.

  7. #7
    Join Date
    Oct 2002
    Posts
    369
    RE:

    I want to commit 10,000 rows at a time. I also need need a routine that will ignore bad records.


    Both requirements may be addressed by currently supported versions of Sql Server using bcp; Bulk Insert is another good option to consider with post 6.5 versions of Sql Server.

  8. #8
    Join Date
    Jan 2003
    Location
    Pnoenix, AZ
    Posts
    8
    Originally posted by rnealejr
    Then I would use bcp or bulk insert. bcp is a command-line utility and bulk insert can be done using dts or transact sql. Let me know if you need an example.

    Please send me an example. Thank You very much

  9. #9
    Join Date
    Oct 2002
    Posts
    369
    RE:

    Please send me an example. Thank You very much


    See the dba_bcp_bulk_insert_example.sql file, (for Sql Server 2k)
    with supporting Arbitrary_SourceAndTarget table
    ddl statements (for different Sql Server versions, check BOL)

    The purpose is to Illustrate bcp / bulk insert using:
    1 FormatFile
    2 BatchSize
    3 MaxErrors
    4 errfile.txt (for bcp only, bulk insert does not support an errfile for Sql Server 2k)

    on a Arbitrary_SourceAndTarget table (which is populated from bulk loads form an external source txt file).

    Execute the: DEMO EXAMPLE statements separately to most clearly see the statement and bcp results.

    NOTE: replace the bcp option -SSqlServer with the appropriate Sql Server instance name
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    bcp can be difficult so I will step you through using bulk insert with DTS.

    1. Open Enterprise Manager and select 'Data Transformation Services'.
    2. Right click on local packages and select 'New Package'.
    3. Under the 'Connection' toolbox select 'MS OLEDB Provider for Sql Server'. This will prompt for connection information - select the appropriate database.
    4. Under the Task toolbox select 'Bulk Insert Task'.
    5. Select the connection/destination table, source data file, format for the text file - for example, comma delimited ... You have other options but I will keep it simple.
    6. Execute/save the package.
    7. Thats it !

    Let me know if you want an example - I will create one using Northwind so you can test it yourself.

Posting Permissions

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