跨数据库数据复制，如果计算机的语言环境不一样要注意事项

select * from tblGroup1Master
select * from tblGroup2Master
select * from tblGroup3Master

--这句是映射一个远程数据库
EXEC sp_addlinkedserver '192.168.3.137',N'SQL Server'

--这句是登录远程数据库
EXEC sp_addlinkedsrvlogin '192.168.3.137','false', NULL, 'sa', 'sasa'

--select * from [192.168.3.137].[WSSys].[dbo].tblGroup3Master

--登录后，可以用以下格式操作远程数据库中的对象   

Merge tblGroup3Master As Target
Using (Select * From  [192.168.3.137].[WSSys].[dbo].tblGroup3Master) As Source
on Target.Code = Source.Code Collate Chinese_Taiwan_Stroke_CI_AS
WHEN MATCHED THEN
UPDATE Set Desci = Source.Desci
WHEN NOT MATCHED BY TARGET THEN
Insert (Code, Desci) Values (Source.Code, Source.Desci);

Merge tblGroup4Master As Target
Using (Select * From  [192.168.3.137].[WSSys].[dbo].tblGroup4Master) As Source
on Target.Code = Source.Code  Collate Chinese_Taiwan_Stroke_CI_AS
WHEN MATCHED THEN
UPDATE Set Desci = Source.Desci
WHEN NOT MATCHED BY TARGET THEN
Insert (Code, Desci) Values (Source.Code, Source.Desci);

Merge tblGroup5Master As Target
Using (Select * From  [192.168.3.137].[WSSys].[dbo].tblGroup5Master) As Source
on Target.Code = Source.Code  Collate Chinese_Taiwan_Stroke_CI_AS
WHEN MATCHED THEN
UPDATE Set Desci = Source.Desci
WHEN NOT MATCHED BY TARGET THEN
Insert (Code, Desci) Values (Source.Code, Source.Desci);

Merge tblGroup6Master As Target
Using (Select * From  [192.168.3.137].[WSSys].[dbo].tblGroup6Master) As Source
on Target.Code = Source.Code  Collate Chinese_Taiwan_Stroke_CI_AS
WHEN MATCHED THEN
UPDATE Set Desci = Source.Desci
WHEN NOT MATCHED BY TARGET THEN
Insert (Code, Desci) Values (Source.Code, Source.Desci);

 

--需要复制的数据的数据表 类别3,4,5,6

 

如果要从简体环境把数据复制到繁体环境，要加后面的红色字体，指明目标数据库的语言环境

1  检查语言的差别

                SELECT         cl.ClientID, cl.ClientTypeCode, ' ' + cl.ClientName AS ClientName,   
                          cl.ClientAddress, ISNULL(cl.Telephone1, '') AS Telephone1,   
                          ISNULL(cl.Telephone2, '') AS Telephone2, ISNULL(cl.Telephone3, '')   
                          AS Telephone3, ISNULL(cl.Telephone4, '') AS Telephone4, ISNULL(cl.Fax, '')   
                          AS Fax, ISNULL(cl.EMail, '') AS EMail, ISNULL(cl.HomePage, '') AS HomePage,   
                          cl.ADPClient, cl.ADPClientID, cl.RegionCode, cl.CreateDate, cl.UpdateDate,   
                          cl.UpdateBy, cl.Status, cl.NeedApproval, cl.NoDiscount, cl.NoPromotion,   
                          ctm.ClientTypeDesci, ISNULL(r.RegionDesci, '') RegionDesci, ISNULL(r.AreaID, -1) AreaID, ISNULL(a.AreaDesci,'') AreaDesci, cl.ApprovalLevel,   
                          ISNULL(cl.PaymentTypeCode, '') AS PaymentTypeCode,   
                          ISNULL(pt.PaymentType, '') AS PaymentType, ISNULL(cl.ResponseBy, '')   
                          AS ResponseBy, ISNULL(cl.PaymentDueDay, 0) AS PaymentDueDay,   
                          ISNULL(cl.Remark, '') AS Remark, ISNULL(cl.OpenTime, '') AS OpenTime,   
                          ISNULL(cl.CloseTime, '') AS CloseTime, cl.Sun, cl.Mon, cl.Tue, cl.Wed, cl.Thu,   
                          cl.Fri, cl.Sat, cl.PublicH, cl.MaxAmtAllowance, cl.PriceSet,   
                          cl.ClientPLUGroup, ISNULL(cl.DocRoute,'') DocRoute  
FROM             dbo.sales_tblClientList cl INNER JOIN  
                          dbo.sales_tblClientTypeMaster ctm ON cl.ClientTypeCode = ctm.ClientTypeCode   Collate Chinese_PRC_CI_AS
        Left JOIN dbo.sales_tblRegion r ON cl.RegionCode = r.RegionCode  Collate Chinese_PRC_CI_AS
        Left JOIN dbo.sales_tblMapArea a ON r.AreaID = a.AreaID   
        LEFT OUTER JOIN dbo.sales_tblPaymentType pt ON cl.PaymentTypeCode = pt.PaymentTypeCode  Collate Chinese_PRC_CI_AS

 

 小结    如果语言不同数据库数据复制，需要对语言进行特殊处理了，处理方案有2

方案1   转移数据的时候，就指定 语言，如上例所示

方案2  如果没有在转移的时候指定，那么可以在修改表结构的时候，修改指定列的语言，修改为默认语言。