SQLTeam.com | Weblogs | Forums

Lookup foreign key when editing data


#1

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?
Thanks!


#2

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


#3

Thanks! Wow.


#4

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 )
go
create trigger t1__tr_ins
on t1
instead of insert 
as
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;
go

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

#5

Also thanks!