Import from Excel to a table with Foreign Key constraint

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?
tinglewebAsked:
Who is Participating?
 
YveauCommented:
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  *
FROM    OPENDATASOURCE
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="X:\Path\To\File.xls";Extended Properties=Excel 8.0')...[AreaName]

Hope this helps ...



0
 
YveauCommented:
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 ...

0
 
tinglewebAuthor Commented:
whats the code for any/all of the steps you mentioned Yveau please?
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.