Bulk inserting with two related tables from Excel

c# sql sqlbulkcopy

Question

I need to use C# WinForms to import data from an excel file into a database. A few columns of information are entered into the "Sites" database for each row, which is a "Electricity Site." Each website also contains a "Contact" with its details (Name, Email, and Phone), although it's possible that this Contact already exists in the database. The value of that contacts' main key (identity field), which functions as a foreign key in the Sites table, is unknown at runtime.

Some of the pertinent information in the Excel file is listed below:

SiteID

ContactName

Email

Phone

The table is as follows:

Contacts
(
    ContactID (identity)
    ContactName
    Email
    Phone 
)

Sites
(
    SiteKey (identity)
    SiteID
    ContactID (foreign key)
)

If the contact's name, email address, and phone number don't already exist in the database, they must be established, and a new identity must be added to that row for Sites (if Contact exists already then that ContactID is needed).

What strategy would be most effective in resolving this issue quickly. In most circumstances, the Excel sheet has 1000 or more rows.

1
0
10/24/2012 4:19:46 PM

Popular Answer

The two Excel files may be bulk-loaded into staging tables using sql bulkcopy and then used with a few "insert into statements that search your actual tables using the correct foreign key.

However, because 1000+ rows is not a large number (depending on the + of course), you could just load them into memory and put them into your target database with the appropriate keys.

0
10/28/2012 12:27:08 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow