Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Unanswered: Bulk Import into multiple tables with foreign keys

    I'm writing a tool to bulk import log files (application activity logs). I've normalized my tables, but I can't figure out how BULK INSERT, OPENROWSET can work with normalized tables. For instance, the logs contain a UserId field. So I have a separeate table for UserIds with a FK relationship to my main table.

    There doesn't seem to be an elgant way to tell BULK IMPORT/OPENROWSET to put some of the data into the main table and some of the data into the Users table. I was thinking I could bulk import twice. once into the main table and once in the the users table with the right format file to skip columsn, but how do I populate the FK (mainTable.UserId) with the coresponding ID from ther usersTable).

    I'm thinking that this is going to be a hack fest (C# or StoredProc), but before I go down that road I want to make sure I'm not missing anything.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just import into a staging table that matches the format of your import file.
    Do what ever scrubbing your data requires (if any) then load into your normalised tables using T-SQL.

    This is how most of us here handle transformations - we don't consider it a hack. If you want to consider other options, you could look in to SSIS but I would recommend the above personally.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Staging tables. Yup.
    Also, make sure you know what the natural keys of your data are.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Just import into a staging table that matches the format of your import file.
    Do what ever scrubbing your data requires (if any) then load into your normalised tables using T-SQL.

    This is how most of us here handle transformations - we don't consider it a hack. If you want to consider other options, you could look in to SSIS but I would recommend the above personally.


    I consider DTS and SSIS a hack

    What did we do before SQL7?
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We scratched data on smooth stones and arranged them in little piles on the cave floor.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Brett Kaiser
    I consider DTS and SSIS a hack

    What did we do before SQL7?
    +1, they are the "cursors" of data loading. I believe anything you do via DTS/SSIS can be done by a stage then load model using BCP/Bulkcopy and T-SQL, albeit a bit more time consuming to develop, but certainly more portable with fewer interfaces. MS went backwards from DTS to SSIS, from the words of Geddy Lee, I refuse to conform, so I shall be cast out.

    I believe a majority of us who came from the UNIX Sybase/Oracle world would agree with Brett.

Posting Permissions

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