将一台数据库服务器数据插入另一台数据库服务器

（用定时任务执行）

vss的数据需要同步到mall里面去：

1.先在mall数据库中定义需要同步的vss数据库的表的同义词
 
(1)同义词：dbusrpub.vss_saleimphead
USE [mall]
GO

/****** Object:  Synonym [dbusrpub].[vss_saleimphead]    Script Date: 11/27/2014 14:37:32 ******/
CREATE SYNONYM [dbusrpub].[vss_saleimphead] FOR [mall].[vss].[dbusrvss].[SALEIMPHEAD]
GO


(2)同义词：dbusrpub.vss_saleimpdet
USE [mall]
GO

/****** Object:  Synonym [dbusrpub].[vss_saleimpdet]    Script Date: 11/27/2014 14:37:50 ******/
CREATE SYNONYM [dbusrpub].[vss_saleimpdet] FOR [mall].[vss].[dbusrvss].[SALEIMPDET]
GO


2.再在mall数据库中写函数进行同步：
USE [mall]
GO
/****** Object:  StoredProcedure [dbusrpub].[sp_getsalefromvss]    Script Date: 11/27/2014 11:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbusrpub].[sp_getsalefromvss]
AS
BEGIN
DECLARE @billno varchar(20)
DECLARE @billsgdh varchar(20)
DECLARE @inputer varchar(20)
DECLARE @inputdate datetime
DECLARE @muid varchar(20)
DECLARE @accdate varchar(20)
DECLARE @sbid varchar(20)
DECLARE @memo varchar(200)

DECLARE det_Cursor CURSOR FOR
  select billno,billsgdh,inputer,inputdate,muid,accdate,sbid,memo from dbusrpub.vss_saleimphead
   where billflag='S' and billno not in(select billno from dbusrpub.saleimphead where billtype='25')
  open det_Cursor;
fetch next from det_Cursor into
              @billno,
              @billsgdh,
              @inputer,
              @inputdate,
              @muid,
              @accdate,
              @sbid,
              @memo;
while @@fetch_status=0  
begin
begin try
BEGIN tran
INSERT INTO SALEIMPHEAD (
        billno,
        billtype,
        billsgdh,
        billflag,
        inputer,
        inputdate,
        muid,
        accdate,
        sbid,
        memo,
        vssbillno
    ) VALUES(
        @billno,
        '25',
        @billsgdh,
        'N',
        @inputer,
        @inputdate,
        @muid,
        @accdate,
        @sbid,
        @memo,
        @billno
    )
delete dbusrpub.SALEIMPDET where billno=@billno
insert into dbusrpub.SALEIMPDET
select * from dbusrpub.vss_saleimpdet where billno=@Billno
commit tran
end try
BEGIN CATCH
        declare @ErrorMessage varchar(500)
        set @ErrorMessage = ERROR_MESSAGE()
        set @ErrorMessage = '失败['+@ErrorMessage+']:'
        raiserror(@ErrorMessage,16,1);     
    ROLLBACK tran
END CATCH;
fetch next from det_Cursor into
              @billno,
              @billsgdh,
              @inputer,
              @inputdate,
              @muid,
              @accdate,
              @sbid,
              @memo;
end
CLOSE det_Cursor;
DEALLOCATE det_Cursor;
END