方案1:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [InsertData]
AS
BEGIN
exec sp_addlinkedserver 'yuancheng','','SQLOLEDB','远程IP'
exec sp_addlinkedsrvlogin 'yuancheng','false',null,'远程数据库帐号','远程数据库密码'
select * into yuancheng.数据库名.dbo.t_test from t_log
delete from t_log
exec sp_dropserver 'yuancheng','droplogins'
END
方案2:
先开启Ad Hoc Distributed Queries组件
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
然后创建存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [InsertData]
AS
BEGIN
insert into openrowset('SQLOLEDB' ,'远程IP';'远程帐号';'远程密码',数据库名.dbo.t_test) select * from t_log
delete from t_log
END
GO
注意,如果远程的t_test表中有主键自增ID,必须先取消此列的标识属性,或者将除了主键列外的字段名在insert语句中列出,不过这样做就不能保证两边数据库的ID一致了