插入截取字符串
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)