May 19, 2006 at 5:57 pm
Hi folks, I am trying to write a script to seed a db with some test values. I have a relationship between two tables; SellingCompany and Salesperson. Salesperson has a CompanyId column that's a FK in SellingCompany and required. CompanyId is also defined as an identity column in SellingCompany, which means it's autogenerated.
'In my script, I start by doing the obvious, creating the company first:
use mydb
go
insert into SellingCompany (CompanyName) values ('Austin West-Side Lyncanthropic Association')
go
'now comes the part where I want to take the id that was just added to SellingCompany and use it in SalesPerson:
insert into SalesPerson (userName, CompanyId) values ('Hank', (select Id from SellingCompany where CompanyName='Austin West-Side Lyncanthropic Association'))
go
When I do this, I get a 'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
Obviously there's a better way to do this, and it seems like such a common requirement that I'm sure that you folks have solved this umpteen times. I'd appreciate any tips someone more knowledgable in SQL might generously offer.
May 19, 2006 at 6:12 pm
Try this:
INSERT INTO SalesPerson (userName, CompanyId)
SELECT 'Hank', Id
FROM SellingCompany
WHERE CompanyName='Austin West-Side Lyncanthropic Association'
That will be fine so long as you don't have multiple Austin West-Side Lycanthropic Association values in SellingCompany.
May 19, 2006 at 6:44 pm
Thank you kindly
May 22, 2006 at 7:18 am
A bit OT, but I love that company name.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply