Recursive update
-
I am trying to combine two tables into one. I have one table that contains an identity, Level1Region, Level2Region, Level3Region, Level4Region and another table that contains a regionID and Region Description.
I want to create a new table that contains all entities of the first table plus a Level1Name, Level2Name, Level3Name, and Level4Name. I need to walk through the second table and match the regionID with each LevelXRegion number and place the region description in the proper regionXname.
Example
RegionID RegionDescription
9 West
206 California
Level1Region Level2Region Level3Region Level4Region
9 Null Null Null
9 206 Null Null
The new table will need to have the RegionDescription for each regionID
Level1Region Level1Name Level2Region Level2Name
9 West Null Null
9 West 206 California
Is that clear what I need done?
-
Try something like:
SELECT R1.*, R2.*
FROM RegionLevels L
JOIN Regions R1
ON L.Level1Region = R1.RegionID
LEFT JOIN Regions R2
ON L.Level2Region = R2.RegionID
-
I want to insert data from two tables into one table. The inserted table is similar in structure to one of the tables that I want to insert from. The latter table to insert from has the level name for each level.
-
Just curious, but what have you come up sp far in solving this problem? It would help to know where you are have a problem.
-
Try this:
create table dbo.RegionDesc (
RegionId int,
RegionDesc varchar(25)
)
create table dbo.RegionLevel (
Level1Region int null,
Level2Region int null,
Level3Region int null,
Level4Region int null
)
create table dbo.RegionsNamed (
Level1Region int null,
Level1Name varchar(25) null,
Level2Region int null,
Level2Name varchar(25) null,
Level3Region int null,
Level3Name varchar(25) null,
Level4Region int null,
Level4Name varchar(25) null
)
go
insert into dbo.RegionDesc values (9, 'West')
insert into dbo.RegionDesc values (206, 'California')
insert into dbo.RegionLevel values (9,null,null,null)
insert into dbo.RegionLevel values (9,206,null,null)
insert into dbo.RegionsNamed
select
rl.Level1Region,
rd1.RegionDesc,
rl.Level2Region,
rd2.RegionDesc,
rl.Level3Region,
rd3.RegionDesc,
rl.Level4Region,
rd4.RegionDesc
from
dbo.RegionLevel rl
left outer join dbo.RegionDesc rd1
on (rl.Level1Region = rd1.RegionId)
left outer join dbo.RegionDesc rd2
on (rl.Level2Region = rd2.RegionId)
left outer join dbo.RegionDesc rd3
on (rl.Level3Region = rd3.RegionId)
left outer join dbo.RegionDesc rd4
on (rl.Level4Region = rd4.RegionId)
go
select * from dbo.RegionsNamed
go
drop table dbo.RegionDesc
drop table dbo.RegionLevel
drop table dbo.RegionsNamed
-
That is exactly what I needed. this worked perfectly. Thank you tremendously.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply