Results 1 to 11 of 11

Thread: Bulk Insert

  1. #1
    Join Date
    May 2004
    Posts
    36

    Unanswered: Bulk Insert

    I'm trying to import data from a text file into my database. each line of this text file must be inserted into 2 seperate tables depending on characters in the line of text. any ideas about how to do this without using a great deal of cpu would be appreciated.


    thanks.....

  2. #2
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Bulk Insert

    IF I understand correctly you are trying to use one Bulk Insert command using single file to load 2 tables at same time depending on some data. As per my understanding , it may be possible if you have triggers on table which would delete rows from primary and add to secondary (depending on character you loking for)

    Or you can insert all the rows in Primary and use another BCP to load to another table using a select statement and later remove those rows from First table

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would load everything to a stagin table, then use sql to split it up to the final destination tables....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    May 2004
    Posts
    36

    Bulk Insert

    another problem i have is that the file contains 128 bit strings that i have to separate into columns of my tables. what would be the best way to get these substrings separated while trying to insert the data into a primary table. if i could do that, then i can just bcp what i need from the primary table over to my secondary one.



    thanks 4 the help....
    e3witt

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is the data delimeted or fixed width?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2004
    Posts
    36
    the data is fixed lenght, not delimited. the only thing i can think of is to look at the substrings that capture each item for my database.

  7. #7
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Dts

    Sorry Double post
    Last edited by aashu; 05-03-04 at 14:06. Reason: Hi

  8. #8
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Dts

    Did you look at DTS. It is Possible to create a dts for this . A column from text file can be mapped to many columns in a table (transform process) . Or

    Load data in some Staging table .
    Use a Select statement to BCP out 2 files using Substring function to breakup the column. Load the text files one at atime to 2 different tables

    If you want you can send table structure and file and may be we could help

  9. #9
    Join Date
    May 2004
    Posts
    36
    the txt file attached is an example, only the one i'll be given has b/t 1000 and 10000 lines.
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    AND header and trailer info.....

    Create a table with 1 column varchar(8000)

    bcp the whole file in to that table

    Get a copy of your record layout and create a select against that table like

    SELECT SUBSTRING(Col1,1,1) AS Col1
    , SUBSTRING(Col1,2,10) AS Col2
    ect

    You can even create it as a view...

    Add a WHERE Clause to eliminate the header and trailer

    You can even do data audits...

    And check the validity of some data...using ISNUMERIC and ISDATE....

    Sound good?

    That's what I do....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    May 2004
    Posts
    36
    thanks... i'll try that today. thank you for ur help.

Posting Permissions

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