SQLTeam.com | Weblogs | Forums

Lookup foreign key when editing data


I have an SQL Server 2014 database. For now, I'm just entering some data by hand ("Edit first 200 rows"). TableA has a foreign key "PNL_id" (type int) to TableB (pnl_id=int, PNL=varchar). I've made an index for the unique name field PNL.So TableB is something like
1 Neil
2 George
3 Fred
4 ...
Question: is there a way to set things up so that I can enter data for PNL_id into the first table TableA, without having to look up the primary key pnl_id? I'd much rather be able to type the name field, "Neil", "George", etc., and have SQLServer figure out which entry it is. Can this be done?


short answer: no
longer answer: this is the kind of thing Integration services was designed for. So instead of editing directly in SSMS, build a CSV file with the new data. The IS package would read it, lookup the foreign key values and combine with the other data to insert into the target table


Thanks! Wow.


You'd need another column that would allow you to enter the name column, because obviously an int column won't accept a string such as 'Neil' or 'George'.

If it's a formal foreign key, you'd likely need an INSTEAD OF INSERT trigger on the table to convert the name to its corresponding id value prior to INSERT.

Edit: The name column in the edited table would be blanked/NULLed out by the trigger, so that it wouldn't permanently take up space in that table.

Here's a quick proof-of-concept example to show that this can actually work :slight_smile: :

use tempdb
if OBJECT_ID('t1') is not null
    drop table t1
if OBJECT_ID('t2') is not null
    drop table t2

create table t2 ( c2 int identity(1, 1) primary key, cname varchar(100) not null )
dbcc checkident (t2, RESEED, 101) /*just so we can tell the diff table ids aparts*/
insert into t2 values('Scott'),('Mike');

create table t1 ( c1 int identity(1, 1) primary key, 
    c2 int foreign key references t2(c2), cname varchar(100) null )
create trigger t1__tr_ins
on t1
instead of insert 
set nocount on;
insert into t1 ( c2, cname )
select case when c2 > 0 then c2 else (select t2.c2 from t2 where t2.cname = i.cname) end as c2, NULL AS cname
from inserted i;

insert into t1 (cname) values('scott'),('mike') /*insert by name*/
insert into t1 (c2) values(102),(101) /*insert by id for name*/
select * from t1


Also thanks!