Bulk Insert Into Table that Requires an Auto Generated ID from another Table

Posted on 2007-08-07
Last Modified: 2013-11-05
I'm trying to avoid using a cursor.  I have a flat table that is populated by a CSV file that the user uploads.  I need to move the data from this table into two other tables.  Once table is called individuals, the other is called individualphonenumbers.  IndividualPhoneNumbers has a column for the IndividualID that is generated in the Individuals Table when a record is inserted.  Is there anyway to do my insert(s) using a bulk insert statement or am I going to be forced to cursor through each record.
Question by:davidcahan
    LVL 35

    Expert Comment

    by:David Todd

    Just a thought - bulk insert into a staging table, then insert/update individuals, then insert/update individualPhoneNumbers.

    My $0.10 worth
    LVL 19

    Accepted Solution

    i usually do what dtodd suggested, into a staging table free of constraints, indexes etc to speed up the load.
    or if you have triggers on the real table you can disable with
    alter table mytable disable trigger mytrigger
    or you can disable/drop whatever constraints and then enable/recreate them

    and then update/insert it afterwards with whatever sql that is more efficient

    Author Comment

    but if i understand what you are both saying, you mean do my insert into the Individuals Table, then update my staging table with those ID's then insert into my PhoneNumbers table using the ID's?
    LVL 35

    Assisted Solution

    by:David Todd

    What we are suggesting is creating a third table that can accept the entire CSV file via a bulk insert. That table will be denormalised. It will be, if you like, a SQL copy of the CSV file. There will be no or few constraints. Do figure out some form of rowID such as an identity ...

    Your process would go like this:
    Bulk insert into this new staging table
    Update/Insert Individuals
    Update/Insert PhoneNumbers - now can use the ID etc from Individuals.



    Author Comment

    yea, i get it.  I was already using the third table.  I just wanted to make sure I understood what you had meant by Update/Insert Individuals


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    Title # Comments Views Activity
    combine data in sql 1 23
    SQL Select * from 6 25
    Why do I get extra rows when I do inner join? 12 29
    SQL Server 2012 express 24 21
    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now