CODEWITHSUNDEEP
sql-serverimportforeign-keyssql-import-wizard
Numan Ahmad
Numan Ahmad

Reputation: 167

How to handle foreign key while importing data from Excel into SQL Server

I have raw data in an Excel sheet. I develop a database in SQL Server with foreign key constraints, now I want to import data from my Excel sheet into my SQL Server database (with multiple tables with foreign key constraints).

Please guide me how can I handle foreign key while importing data into my database? I am new to SQL Server database, any tutorial link will be helpful.

Thanks in advance.

Upvotes: 1

Views: 4205

Answers (3)

alp
alp

Reputation: 702

it's very easy and reliable to do with SSIS Import Wizard, I did it many times with complex schemas.

  • Make sure your PK column is defined as IDENTITY so it can increment by 1 per record import, then make sure all the FKs columns in other tables are also IDENTITY and have a relationship with the PK.

  • build an import Excel workbook, with one sheet per table, and with column names as they are in your DB tables; make sure the format of cells match the data type of your DB columns

  • run the import wizard, choose Excel as source and OLE DB Provider for SQL Server as destination, map each worksheet to their db table, run the import.

If the schema/setup are well made, it works perfectly everytime.

Upvotes: 0

JohannesGbg
JohannesGbg

Reputation: 101

If you can use SQL Server Integration Services, a lookup transformation would do the work for you. This is an example: http://www.learnmsbitutorials.net/ssis-lookup-transformation-example.php

We also offer an Excel Add-In to import and update data in SQL Server from Excel. It has the possibility to create drop-down lists in Excel for foreign key relations. The user will then select a text item in Excel (fetched from the foreign key table) and the corresponding key value will be inserted into the table in SQL Server. More details are available here: https://sqlspreads.com

Upvotes: 0

PSK
PSK

Reputation: 17943

You can't rely on a data which is being exported using Excel. I suggest you following approach.

  1. Dump the data into a intermediate table without any constraint.

  2. Validate the data before moving to the main table. For this you can use where exists clause to check if the data is present in the foreign key table or not.

  3. Records which are failed in validation, you can report them back to the user.

Upvotes: 1

Related Questions