日期:2014-05-18  浏览次数:20640 次

插入截取字符串
table1:

co1
ab
df


table2
co2
abc
efg
hij
mno
如何插入根据table2中co2字段中前两位在   table1   中没有的hi   和mn
两条记录。
最好用   if   exists()   begin
insert


------解决方案--------------------
insert into table1
select left(col2,2) from table2 b
where not exists(select 1 from table1 a where a.col1=left(b,2))
------解决方案--------------------
insert into table1
select left(co2,2) from table2 b
where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
------解决方案--------------------
或者

if not exists(select 1 from table1 a where co1 in ( 'hi ', 'mn '))
begin
insert into table1
select 'hi ' union select 'mn '
end
------解决方案--------------------
if exists(select 1 from table2 where left(co2,2) not in(select co1 from table1))
begin
insert table1(co1) select distinct left(col2,2)
where left(co2,2) not in(select co1 from table1))
end
------解决方案--------------------
gahade(与君共勉)正确但楼主意思不甚明确,补充一句
------解决方案--------------------
insert into table1
select left(co2,2) from (select case when left(co2,1)= 'h ' then 'h ' when left(co2,1)= 'm ' then left(co2,3) else co2 end from table2)b
where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
------解决方案--------------------
insert into table1
select left(co2,2) from table2 b
where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
------解决方案--------------------
insert into table1
select case left(co2,1) when 'h ' then left(co2,1) when 'm ' then left(co2,3) else left(co2,2) end
from table2 b
where not exists(select 1 from table1 a where a.co1=case left(co2,1) when 'h ' then left(co2,1) when 'm ' then left(co2,3) else left(co2,2) end)