第一,本地数据库跨库更新
1.插入语句
INSERT INTO B (Column1, Column2, Column3)
SELECT Column1, Column2, Column3
FROM DatabaseB.dbo.TableB AS B
INNER JOIN DatabaseA.dbo.TableA AS A
ON B.Id = A.Id
2.更新语句
UPDATE B
SET B.Column1 = A.Column1,
B.Column2 = A.Column2
FROM DatabaseB.dbo.TableB AS B
INNER JOIN DatabaseA.dbo.TableA AS A
ON B.Id = A.Id
第二,两个数据库位于不同的SQL Server实例上,可以使用链接服务器
1.创建链接服务器
EXEC sp_addlinkedserver
@server = 'RemoteServer', -- 链接服务器的名称
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = 'RemoteServerName'; -- 远程服务器的名称或IP地址
2.配置链接服务器的登录信息
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'RemoteServer',
@useself = 'false',
@rmtuser = 'RemoteUsername', -- 远程服务器的用户名
@rmtpassword = 'RemotePassword'; -- 远程服务器的密码
3.测试链接服务器连接
EXEC sp_testlinkedserver 'RemoteServer';
4.从本地服务器插入数据到远程服务器
INSERT INTO RemoteServer.RemoteDatabase.dbo.RemoteTable (Column1, Column2)
SELECT Column1, Column2
FROM LocalDatabase.dbo.LocalTable;
5.从本地服务器更新远程服务器上的数据
UPDATE RemoteServer.RemoteDatabase.dbo.RemoteTable
SET RemoteTable.Column1 = LocalTable.Column1,
RemoteTable.Column2 = LocalTable.Column2
FROM LocalDatabase.dbo.LocalTable AS LocalTable
JOIN RemoteServer.RemoteDatabase.dbo.RemoteTable AS RemoteTable
ON LocalTable.Id = RemoteTable.Id;