Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1266
  • Last Modified:

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?
0
tingleweb
Asked:
tingleweb
  • 3
1 Solution
 
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:
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:
Glad I could be of any help and thanks for the grade !
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now