June 11, 2003 at 10:49 am
Basically, we have a "Router" table with PK: RouterID and FKs: SiteID, TierID, IpID, LanID that relate to the "Site", "Tier", "Ip_Address" and "Lan" table respectively. Every Router will have an associated Site, Tier, Ip and LanIF. But we're having trouble building an insert statement for inserting a new record into the "Router" table. Here's what we have but it returns "0 rows affected" and doesn't insert a row. What are we doing wrong?
Insert Into Router
(RouterID, SiteID, TierID, IpID, LanID, Hostname, R_O, R_W)
Select NewID(), Site.SiteID, Tier.TierID, Ip_Address.IpID, Lan.LanID, 'AR23005A', 'ro' ,'rw'
From Router
Inner Join Site ON Router.SiteID = Site.SiteID
Inner Join Tier ON Router.TierID = Tier.TierID
Inner Join Ip_Address ON Router.IpID = Ip_Address.IpID
Inner Join Lan ON Router.LanID = Lan.LanID
Thanks,
Joey
June 11, 2003 at 10:54 am
If you run the select without the insert does it return any rows? that's likely where your issue lies.
Steve Jones
June 11, 2003 at 11:02 am
But if ran the select without the insert which table would I select from? All 4 Site, Tier, Ip_Address & Lan? They are all populated except the Router table which is empty. We're trying to write SP's to handle inserts into the Router table.
June 11, 2003 at 11:43 am
If you INNER JOIN your tables to an empty table, you will always insert 0 records, since the JOIN won't find any matching records. I believe you want something like:
Insert Into Router
(RouterID, SiteID, TierID, IpID, LanID, Hostname, R_O, R_W)
Select NewID(), Site.SiteID, Tier.TierID, Ip_Address.IpID, Lan.LanID, 'AR23005A', 'ro' ,'rw'
FROM Site, Tier, Ip_Address, Lan
This will give you a router ID for each of the possible combinations of Site, Tier, IP Address, and LAN...
June 11, 2003 at 11:53 am
Awesome! That worked! So I didn't even need a join...
Thanks a lot man,
Joey
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply