如题,通过每隔5隔小时在表B中插入数据,jx,ch。
而jx,ch是从远程服务器表A中jcxh,ch读取。因为不能往表B中插入重复数据,写了如下语句:
insert into dbo.表B (jx,ch,cr)
select jcxh,ch,cr
from OPENROWSET('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',数据库.dbo.表A)
where cr='入'
and not exists (select jx,ch from 表B where
LEFT(shi,10)=LEFT(CONVERT(VARCHAR,GETDATE(),120),10) )但是结果不对,语法没问题。请大侠们再帮我看下该怎么写?
而jx,ch是从远程服务器表A中jcxh,ch读取。因为不能往表B中插入重复数据,写了如下语句:
insert into dbo.表B (jx,ch,cr)
select jcxh,ch,cr
from OPENROWSET('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',数据库.dbo.表A)
where cr='入'
and not exists (select jx,ch from 表B where
LEFT(shi,10)=LEFT(CONVERT(VARCHAR,GETDATE(),120),10) )但是结果不对,语法没问题。请大侠们再帮我看下该怎么写?
select jcxh,ch,cr
from OPENROWSET('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',数据库.dbo.表A) AS T
where cr='入'
and not exists (select jx,ch from 表B where
LEFT(shi,10)=LEFT(CONVERT(VARCHAR,GETDATE(),120),10) )
select
jcxh,ch,cr
from ]
OPENROWSET
('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',数据库.dbo.表A)
AS T
where
cr='入'
and
not exists (select jx,ch from 表B where
LEFT(shi,10)=LEFT(CONVERT(VARCHAR(10),GETDATE(),120),10)) 应该是你后面时间取值问题
select
jcxh,ch,cr
from
OPENROWSET
('SQLOLEDB','DRIVER={SQL Server};SERVER=192.168.21.188;UID=sa;PWD=',数据库.dbo.表A)
AS T
where
cr='入'
and
not exists (select jx,ch from 表B where
LEFT(shi,10)=LEFT(CONVERT(VARCHAR(10),GETDATE(),120),10))
上面多了个符号,测试一下
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)