Easiest way to insert lookup data along with master data

batchakamal
batchakamal used Ask the Experts™
on
I have a master table in which more than 25 foreign keys are there pointing to different lookup tables.

For eg, I have a table called StudentMaster with

StudentID (PK),
StudentName,
CountryId (FK),
MartialStatus (FK),
Degree (FK),
MajorSubject (FK),
MinorSubject (FK),
University (FK) etc..

Now I am importing data from a temporary table and they have provided the values instead of codes in the Foreign Key values. Now I need to write a script to import these data into my table. When importing data if any lookup value found I need to insert them into the lookup table and then insert the master data along with new lookup codes.

Please advice..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
First migrate all the lookup tables first using a script. Then migrate data to the table (master table as you have mentioned) by joining the lookup tables. Also you can use series of update scripts to update master table after migrating only the non fk columns for the master table.
batchakamalAdvisor

Author

Commented:
Yes, I know this basic.

How can I do this just in one step... can we use MERGE??
Chief Technology Officer
Most Valuable Expert 2011
Commented:
You can create a view that joins the tables in question and then you can use an INSTEAD OF INSERT trigger on that table in which you INSERT/MERGE data for lookup tables first and then using the key from each of those tables do INSERT/MERGE to master table.

The inserts have to be done one at a time from SQL's perspective, but the view with trigger allows you to do insert at once from client perspective.
batchakamalAdvisor

Author

Commented:
I am expecting something more easier than this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial