Import from Excel to a table with Foreign Key constraint

Posted on 2007-10-11
Last Modified: 2012-08-13
I'm using the SQL Server 2005 integration service to import from Excel to SQL Server.  Unfortunately, it fails as it wants to truncate my 'Product' table and this isnt allowed as its connected to a table called 'BasketItem' on a foreign key constraint (ProductID).

What do you recommend I could do so it doesnt have the truncate conflict?
Question by:tingleweb
    LVL 18

    Expert Comment

    1. If you are sure you won't get into any trouble (RI wise) after importing the table from Excel, you could disable the FK and truncate the table during the import.
    2. you could import the Excel sheet into a Product_import table and from there add the differences from that table to the real Product table.
    3. you could run a script to import only those rows that are new or modified in Excel and import those into SQL

    Hope this helps ...


    Author Comment

    whats the code for any/all of the steps you mentioned Yveau please?
    LVL 18

    Accepted Solution

    1. Turn off the fk constraint: ALTER TABLE dbo.Y2 NOCHECK CONSTRAINT fkY2Y1
        Turn it back on again: ALTER TABLE dbo.Y2 CHECK CONSTRAINT fkY2Y1

    2. Use the wizard to import the complete sheet to table Y1. Then find the records not yet in Y2 and insert them from Y1 into Y2 running this SQL code:
    insert  into Y2
    select  Y1.*
    from    Y1
    left    outer join Y2
    on      Y1.col1 = Y2.col1
    where   Y2.col1 is NULL

    3.1. In the excel sheet, select a range that you want as a table.
    3.2. Select from the menu insert, name, define ...
    3.3. Give in a name for the selected area.
    3.4. Close the excel sheet.
    3.5. Run the SQL script:
    insert into <Table>
    SELECT  *
    (       'Microsoft.Jet.OLEDB.4.0'
    ,       'Data Source="X:\Path\To\File.xls";Extended Properties=Excel 8.0')...[AreaName]

    Hope this helps ...

    LVL 18

    Expert Comment

    Glad I could be of any help and thanks for the grade !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now