求SQL语句或存储过程。。。。。。。。。。。。。

[yzg614]

有两个表
表A
A.ID A.NAME
1 AAA
2 BBB
3 CCC
4 DDD
5 EEE
表B
B.ID B.PATH
1 AAA
2 AAA
3 AAA
4 DDD
5 DDD
6 EEE
7 GGGGGGG
8 KKKKKKK
9 GGGGGGG
现在将B表中。PB。ATH字段换成A表中的ID字段 即象下面这样
B.ID B.NAME
1 1
2 1
3 1
4 4
5 4
6 5
7
8
9
有种情况是B表中还有两天记录 ID=7和ID=8,ID=9的在A表中并不存在。如果不存在就向A表中插入记录 取得A表中插入记录的ID号再更新B表中的PATH字段 .哪位大侠帮帮忙。谢谢

[$扫地僧$]

declare @t table(ID int,NAME varchar(20))
insert into @t select 1,'AAA'
union all select 2,'BBB'
union all select 3,'CCC'
union all select 4,'DDD'
union all select 5,'EEE'

declare @a table(ID int,PATH varchar(20))
insert into @a select 1,'AAA'
union all select 2,'AAA'
union all select 3,'AAA'
union all select 4,'DDD'
union all select 5,'DDD'
union all select 6,'EEE'
union all select 7,'GGGGGGG'
union all select 8,'KKKKKKK'
union all select 9,'GGGGGGG'


select a.ID,isnull(T.NAME,'') as Name from @a A ,@t T where A.PATH*=T.NAME
--或者:
select A.ID,isnull((select NAME from @t where NAME=A.PATH),'') from @a A

[yuweiwei]

楼主,表A.ID 是自增列的吗?如果是的话可以这样.

create table a(ID int identity(1,1),NAME varchar(20))
insert into a select 'AAA'
union all select 'BBB'
union all select 'CCC'
union all select 'DDD'
union all select 'EEE'

create table b(ID int,PATH varchar(20))
insert into b select 1,'AAA'
union all select 2,'AAA'
union all select 3,'AAA'
union all select 4,'DDD'
union all select 5,'DDD'
union all select 6,'EEE'
union all select 7,'GGGGGGG'
union all select 8,'KKKKKKK'
union all select 9,'GGGGGGG'

用存储过程实现:
create procedure ts
as
begin
insert into a select distinct b.path from b where b.path not in( select a.name from a )
update b set b.path=a.id from a,b where a.name=b.path
end

exec ts

[xeqtr1982]

--这样？
declare @t table(ID int,NAME varchar(20))
insert into @t select 1,'AAA'
union all select 2,'BBB'
union all select 3,'CCC'
union all select 4,'DDD'
union all select 5,'EEE'

declare @a table(ID int,PATH varchar(20))
insert into @a select 1,'AAA'
union all select 2,'AAA'
union all select 3,'AAA'
union all select 4,'DDD'
union all select 5,'DDD'
union all select 6,'EEE'
union all select 7,'GGGGGGG'
union all select 8,'KKKKKKK'
union all select 9,'GGGGGGG'

update b set b.path=isnull(cast(a.id as varchar),'') from @t a,@a b where a.name=*b.path
select * from @a

[dp_555]

友情接分。。。

[popohei]

create table a(ID int,NAME varchar(20))
insert into a select 1,'AAA'
union all select 2,'BBB'
union all select 3,'CCC'
union all select 4,'DDD'
union all select 5,'EEE'

create table b(ID int,PATH varchar(20))
insert into b select 1,'AAA'
union all select 2,'AAA'
union all select 3,'AAA'
union all select 4,'DDD'
union all select 5,'DDD'
union all select 6,'EEE'
union all select 7,'GGGGGGG'
union all select 8,'KKKKKKK'
union all select 9,'GGGGGGG'

SELECT b.ID AS ID, a.ID AS Name
FROM b LEFT OUTER JOIN
a ON a.NAME = b.PATH

[冷箫轻笛]

向A表插入数据的时候，怎么确定NAME字段的值那？