SQL Server 跨库同步数据

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

create PROCEDURE [dbo].[p_pm_项目平台客户批量同步到报销平台](

@destserver nvarchar(50),

@sourceserver nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON;

–不存在则添加链接服务器,外部查询必须指明IP地址,例如 select * from [IP].[database].[dbo].[table]

if not exists (select * from sys.servers where server_id!=0 and data_source=@destserver)

begin

exec sp_addlinkedserver @server=@destserver

end

if not exists (select * from sys.servers where server_id!=0 and data_source=@sourceserver)

begin

exec sp_addlinkedserver @server=@sourceserver

end

begin try

set xact_abort on

begin transaction

INSERT INTO [192.168.0.10].[dbCRM].[dbo].[t_custom] (客户ID,

客户名称,

客户简称,

输入码,

查询码,

地址,

录入登录名,

录入时间,

修改登录名,

修改时间,

审批状态ID,

审批状态名称,

是否审批结束,

审批操作时间,

项目管理客商编码,

序号)

SELECT A.客户ID,A.客户名称,

A.客户简称,

dbo.fn_pm_GetPy(A.客户名称),

A.客户编号+','+A.客户名称+','+dbo.fn_pm_GetPy(A.客户名称)+','+A.客户简称+','+dbo.fn_pm_GetPy(A.客户简称),

A.地址,

'admin',

getdate(),

null,

null,

'D65F87A8-79C8-4D1C-812D-AE4591E056A8',

'已审批',

1,

A.审批操作时间,

A.项目管理客商编码,

0

FROM [dbPM].[dbo].[v_custom] A

WHERE A.客户ID NOT IN ( SELECT 客户ID FROM [192.168.0.10].[dbCRM].[dbo].[t_custom]);

———————————-存在更新———————————–

update A set

A.客户名称=B.客户名称,

A.客户简称=B.客户简称,

A.输入码=dbo.fn_pm_GetPy(B.客户名称),

A.查询码=B.客户编号+','+B.客户名称+','+dbo.fn_pm_GetPy(B.客户名称)+','+B.客户简称+','+dbo.fn_pm_GetPy(B.客户简称),

A.地址=B.地址,

A.修改登录名='admin',

A.修改时间=getdate(),

A.项目管理客商编码 =B.项目管理客商编码

from [192.168.0.10].[dbCRM].[dbo].[t_custom] A,[dbPM].[dbo].[v_custom] B

where A.客户ID=B.客户ID;

commit transaction

end try

begin catch

select ERROR_NUMBER() as errornumber,ERROR_MESSAGE() as errormsg,ERROR_LINE() as errorline

rollback transaction

end catch

END