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 如果没有在转移的时候指定,那么可以在修改表结构的时候,修改指定列的语言,修改为默认语言。