Can I do a foreign key lookup while importing data into SQL Server 2005?

Question

Background

I have a spreadsheet of data that I'm importing into SQL Server 2005. I'm using the Import Wizard in Management Studio to do this.

Problem

I'm importing the data into Table A. Table A has a foreign key reference to Table B. My spreadsheet contains a value in Table B and I need the key for that value so I can put it into Table A. Is there a way to do this lookup as a part of the import wizard?

Answer 1

The import wizard should allow you to create your own query for import rather than its own.

So, with that, you can do something like:

INSERT INTO tableA(c1, c2, c3)
SELECT c1, c2, TableB.pk 
FROM 
  inputtable 
  INNER JOIN TableB ON TableB.anotherfield = inputtable.bcol