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

Answer 2

If you have standard or enterprise version, you can use SSIS (BI development studio) to edit the package the wizard creates. Use Lookup transformation to get the key. If you happen to have trouble with data types (frequent when using Excel source) use Data Conversion transformation after the Excel source,