更新bs已经有的
update tt
set tt.LXBH=t1.LXBH,
tt.LBBH=t1.LBBH,
tt.ffrq=t1.ffrq--必须要加上,不然就会出错
from (select * from T where (DATEDIFF([day], FFRQ, getdate()) = 0)) t1,njgf.book.dbo.T tt
where tt.bs =t1.bs
--插入不存在的
insert into njgf.book.dbo.T
select *
from T
WHERE (DATEDIFF([day], FFRQ, getdate()) = 0)
and T.BS not in
(select BS from njgf.book.dbo.T
where (DATEDIFF([day], FFRQ, getdate()) = 0)
)
你参考一下
用selet * into 肯定不行。
用dts比较方便
1、如果是在sql server中就用触发器实现
2、如果想用ado实现
如果是完全保持一致 ,在第一次同步时
con.execute "Delete From Table B"
insert into b select * from A
然后从表B中删除所有A表的数据
Delete from B where 主键 in(select 主键 from A)
添加A表的数据到B表中
insert into B select * from A
不知道楼主用过sql server的导入和导出功能没有,系统提供的方法好象也是通过insert语句实现的。
设有A表(ID,Name),B表(ID,Name).其中ID为主键。
现将B表中不存在A表的数据插入A表
SQL语句如下:
insert into A(ID,name)
select B.id,B.name from B where B.id not in(SELECT B.id
FROM B,A where (B.id=A.id))
例子:
Dim Conn As New ADODB.Connection
Dim Strconn As String
Strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabasePath & ";Persist Security Info=False"
Conn.CursorLocation = adUseClient
Conn.Open Strconn
Rs.Open "Select * into table from " & TableName, Conn, adOpenStatic, adLockBatchOptimistic