有两个数据库(A,B),里面分别有两个表t1,t2
A库中有t1表,B库中有t2表
t1表结构如下:
a(int) b(int) s(int)
1234 12345 10
2545 11425 9
2564 11965 11t2表结构如下:
a(float) b(float) s(int)
12.34 123.45 10
25.45 114.25 9
25.64 119.65 11
29.64 69.65 11要求将t2表中存在的记录,t1表中不存在的记录,添加到t1表中
(t2.a,t2.b分别乘以100取整与t1.a,t1.b比较,a与b字段同时相等才表示记录相同)
A库中有t1表,B库中有t2表
t1表结构如下:
a(int) b(int) s(int)
1234 12345 10
2545 11425 9
2564 11965 11t2表结构如下:
a(float) b(float) s(int)
12.34 123.45 10
25.45 114.25 9
25.64 119.65 11
29.64 69.65 11要求将t2表中存在的记录,t1表中不存在的记录,添加到t1表中
(t2.a,t2.b分别乘以100取整与t1.a,t1.b比较,a与b字段同时相等才表示记录相同)
use a
insert into t1
select * from b..t2
where not exists (select 1 from
t1 where t1.a=t2.a and t1.b=b2.b)
试试下面的sql:
insert into A.t1 (
id,a,b,s
)
t1_sq.next,--t1表的序列值
select bt.a,bt.b,bt.s from B.t2 bt where bt.id not in (
select bt.id from A.t1 at ,B.t2 bt where round(at.a,0) = round(bt.a,0)
and round(bt.b,0) = round(bt.b,0)
)
select a,b,s
from
(select cast(floor(100*a) as int) a,cast(floor(100*b) as int) b,s
from t2)a
where not exists (select 1 from t1 where a=a.a and b=a.b)
insert into A.t1 (
id,a,b,s
)
t1_sq.next,--t1表的序列值
select bt.a,bt.b,bt.s from B.t2 bt where bt.id not in (
select bt.id from A.t1 at ,B.t2 bt where round(at.a,0) = round(bt.a*100,0)
and round(at.b,0) = round(bt.b*100,0)
)
select a,b,s
from
(select cast(floor(100*a) as int) a,cast(floor(100*b) as int) b,s
from B.t2)a
where not exists (select 1 from t1 where a=a.a and b=a.b)
这样写提示B.t2无效
from DBname.dbo.tb
insert into 库名.dbo.t1
select * from 库名.dbo.t2 b
where not exists
(select 1 from 库名.dbo.t1 a where a.a=cast(b.a*100 as int) and a.b=cast(b.b*100 as int))
用户名.表名 的形式。
若是不能加Id,就得使用 not exists
然后用linkedname.dbname.objectname.tablename来引用
select floor(t2.a*100),floor(t2.b*100),t2.s
From t1
Left Outer Join t2 on t1.a = floor(t2.a*100) and t1.b= floor(t2.b*100)
Where t1.a Is Null
use A
insert into t1(a,b,s)
from(select floor(t2.a*100),floor(t2.b*100),t2.s
from B.t2
where not exists (
select *
from B.t2,t1
where t1.a=floor(t2.a*100)
and t1.b=floor(t2.b*100)
)
)
insert into A..t1
select t2.a,t2.b,t2.s
from B..t2 as t2
inner join A..t1 as t1
on 100*t2.a<>t1.a or 100*t2.b<>t1.b
试试