极度挑战如何把一张表的数据插入到另一张表

[myyilian21]

假发有两表a,b
a 表在远程服务器,这样在打OpenDataSource( 'SQLOLEDB','uid=sa;pwd=sa;Database=CQFFSMS;serVer=COMPANY-DNAVS4J\ZYC').mydb.dbo.mytable

b表为本地表
现在要把b表的记录插入到A表 并且,where 子句里保证 a表的name字段和b表的name不同时才插入

[fa_ge]

select * into a from openrowset('sqloledb','IP';'sa';'sa','select * from mydb.dbo.b where a.name<>b.name')

[lwl0606]

insert into OpenDataSource( 'SQLOLEDB','uid=sa;pwd=sa;Database=CQFFSMS;serVer=COMPANY-DNAVS4J\ZYC').mydb.dbo.mytable
SELECT * FROM b where name not in (select distinct name from OpenDataSource( 'SQLOLEDB','uid=sa;pwd=sa;Database=CQFFSMS;serVer=COMPANY-DNAVS4J\ZYC').mydb.dbo.mytable )

[iseelxj]

insert into OpenDataSource( 'SQLOLEDB','uid=sa;pwd=sa;Database=CQFFSMS;serVer=COMPANY-DNAVS4J\ZYC').mydb.dbo.mytable a
SELECT * FROM b where name not exists (select 1 from where a.name=b.name)

[iseelxj]

insert into OpenDataSource( 'SQLOLEDB','uid=sa;pwd=sa;Database=CQFFSMS;serVer=COMPANY-DNAVS4J\ZYC').mydb.dbo.mytable a
SELECT * FROM b where b.name not exists (select 1 from a where a.name=b.name)

[ojuju10]

--方法一：
insert into OpenDataSource( 'SQLOLEDB','uid=sa;pwd=sa;Database=CQFFSMS;serVer=COMPANY-DNAVS4J\ZYC').mydb.dbo.mytable a(col1,col2,...)
SELECT col1,col2,... FROM b where name not exists (select 1 from where a.name=b.name)

--方法二，建立链接数据库后,直接进行操作
insert into CQFFSMS.mydb.dbo.mytable a(col1,col2,...)
SELECT col1,col2,... FROM b where name not exists (select 1 from where a.name=b.name)

[ojuju10]

有关建立链接数据库，请参考系统两个存储过程：
sp_addlinkedserver

sp_addlinkedsrvlogin

[DavidNoWay]

顶！！