CODEWITHSUNDEEP
sql-serversql-server-2005ssmssql-import-wizard
The Matt
The Matt

Reputation: 6636

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

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?

Upvotes: 2

Views: 1029

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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,

Upvotes: 0

richardtallent
richardtallent

Reputation: 35363

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

Upvotes: 4

Related Questions