我想每隔5个小时对表B插入数据jx,ch两列。而jx和ch是从表A中读出的jcxh和ch两列,因为不能在表B中添加重复数据,所以希望添加的时候先判断添加过的就不要再添了。
我写的语句如下:
insert into 表B (jx,ch)
select jcxh,ch
from 表A
where jcxh not in (select jx from 表B )
and ch not in (select ch from 表B)
但是实际的结果好像是所有表B中含有的jx都不能添加,所有表B中含有的ch也都不能添加了。
我写的语句如下:
insert into 表B (jx,ch)
select jcxh,ch
from 表A
where jcxh not in (select jx from 表B )
and ch not in (select ch from 表B)
但是实际的结果好像是所有表B中含有的jx都不能添加,所有表B中含有的ch也都不能添加了。
insert into tb(jcxh,ch)
select
1,3 union all select
1,2 union all select
2,4 union all select
2,5 union all select
3,2 union all select
3,1 union all select
4,5 union all select
4,7 union all select
5,5 union all select
6,6;select * from tb;select * from tb t
where not exists (select jcxh,ch from tb where jcxh=ch and jcxh=t.jcxh and ch=t.ch )
select jcxh,ch
from 表A A
where NOT EXISTS(SELECT 1 FROM B WHERE A.JCXH=JCXH AND CH=A.CH)
where not exists(select 1 from 表B where jx = 表A.jcxh and ch = 表A.xh)
drop table A
go
create table A(
jcxh varchar(100),
ch varchar(100)
)
insert into A
select '1','一'
union all
select '4','4'
go
if(object_id('B') > 0)
drop table B
go
create table B(
jx varchar(100),
ch varchar(100)
)insert into B
select distinct * from A where a.jcxh not in (select jx from B) and a.ch not in (select ch from B)/*
insert into B
select distinct a.* from A a left join B b on a.jcxh = b.jx and a.ch = b.ch where b.jx is null
*/select * from B
select * from A
insert into dbo.T_jichechuru (jx,ch,cr,shi,location)
select jcxh,ch,cr,t_date=convert(varchar(20),CAST(t_date as datetime),120) ,location
from OPENROWSET('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',yyk.dbo.机车出入库情况)
AS T
where convert(varchar(10),CAST(t_date as datetime),120)=LEFT(CONVERT(VARCHAR,GETDATE(),120),10)
and cr='入'
and not exists(select 1 from T_jichechuru where jx = T.jcxh and ch = T.xh)
insert into dbo.T_jichechuru (jx,ch,cr,shi,location)
select jcxh,ch,cr,t_date=convert(varchar(20),CAST(t_date as datetime),120) ,location
from OPENROWSET('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',yyk.dbo.机车出入库情况)
AS T
where convert(varchar(10),CAST(t_date as datetime),120)=LEFT(CONVERT(VARCHAR,GETDATE(),120),10)
and cr='入'
and not exists(select 1 from T_jichechuru where jx = T.jcxh and ch = T.xh)