今天干了件力活,手工写语句来完成数据库同步.

SERVER=192.168.1.15;UID=webdev;PWD=web123dev;



這個這個。。。可以連上去伐?

/*add romate vendor database server*/

EXEC sp_addlinkedserver 

   'VendorDB', 

   '', 

   'MSDASQL',

   NULL,

   NULL,

   'DRIVER={SQL Server};SERVER=192.168.1.15;UID=webdev;PWD=web123dev;'

GO



DECLARE @maxCustomerID INT

DECLARE @maxSaleOrderID INT

DECLARE @maxSaleOrderItemID INT



SELECT @maxCustomerID = 0,@maxSaleOrderID = 0,@maxSaleOrderItemID = 0



/*begin update game data for sale*/

--delete the game not exists

DELETE a FROM Game a

	WHERE a.GameID NOT IN(SELECT GameID FROM VendorDB.Vcsale.dbo.Game)

--update rows exist

UPDATE a SET a.Name=b.Name,

		a.GoldName = b.GoldName,

		a.GoldUnit = b.GoldUnit,

		a.TradeMode = b.TradeMode,

		a.DirectMode = b.DirectMode,

		a.Type = b.Type,

		a.StockType = b.StockType,

		a.SaleStatus = b.SaleStatus

	FROM game a

INNER JOIN VendorDB.Vcsale.dbo.Game b

	ON a.GameID=b.GameID

		AND CHECKSUM(a.Name,a.GoldName,a.GoldUnit,a.TradeMode,a.DirectMode,a.Type,a.StockType,a.SaleStatus)

			!= 

			CHECKSUM(b.Name,b.GoldName,b.GoldUnit,b.TradeMode,b.DirectMode,b.Type,b.StockType,b.SaleStatus)

--insert new rows

INSERT Game

	SELECT a.* FROM VendorDB.Vcsale.dbo.Game a

LEFT JOIN Game b

	ON a.GameID=b.GameID

	WHERE b.GameID IS NULL

/*end update game data for sale*/



/*begin update gameserver data for sale*/

--delete the server not exists

DELETE a FROM GameServer a

	WHERE NOT EXISTS(SELECT 1 FROM VendorDB.Vcsale.dbo.GameServer WHERE a.GameServerID = GameServerID)

--update rows exist

UPDATE a SET a.GameID = b.GameID,

		a.Name = b.Name,

		a.SaleStatus = b.SaleStatus,

		a.Price = b.Price,

		a.SearchCode = b.SearchCode

	FROM GameServer a

INNER JOIN VendorDB.Vcsale.dbo.GameServer b

	ON a.GameServerID = b.GameServerID

		AND CHECKSUM(a.GameID,a.Name,a.SaleStatus,a.Price,a.SearchCode)

			!=

			CHECKSUM(b.GameID,b.Name,b.SaleStatus,b.Price,b.SearchCode)

--insert new rows

INSERT GameServer

	SELECT a.* FROM VendorDB.Vcsale.dbo.GameServer a

LEFT JOIN GameServer b

	ON a.GameServerID = b.GameServerID

	WHERE b.GameServerID IS NULL

/*end update gameserver data for sale*/





/*begin update tradeaddress data for sale*/

--delete the tradeaddress not exists

DELETE a FROM TradeAddress a

	WHERE a.TradeAddressID NOT IN(SELECT TradeAddressID FROM VendorDB.Vcsale.dbo.TradeAddress)

--update row exists

UPDATE a SET a.GameID = b.GameID,a.Address = b.Address

	FROM TradeAddress a

INNER JOIN VendorDB.Vcsale.dbo.TradeAddress b

	ON a.TradeAddressID = b.TradeAddressID

		AND CHECKSUM(a.GameID,a.Address) != CHECKSUM(b.GameID,b.Address)

--insert new rows

INSERT TradeAddress

	SELECT a.* FROM VendorDB.Vcsale.dbo.TradeAddress a

LEFT JOIN TradeAddress b

	ON a.TradeAddressID = b.TradeAddressID

	WHERE b.TradeAddressID IS NULL

/*end update tradeaddress*/



/*begin update systemconfig*/

UPDATE a SET a.SaleSiteInfoWhenClose = b.SaleSiteInfoWhenClose,

		a.SaleSiteStatus = b.SaleSiteStatus

	FROM SystemConfig a

INNER JOIN VendorDB.Vcsale.dbo.SystemConfig b

	ON 1=1 AND CHECKSUM(a.SaleSiteInfoWhenClose,a.SaleSiteStatus)

			!=

			CHECKSUM(b.SaleSiteInfoWhenClose,b.SaleSiteStatus)

/*end update systemconfig*/



/*begin update saleblock*/

--delete the saleblock not exists

DELETE a FROM SaleBlock a

	WHERE NOT EXISTS(SELECT 1 FROM VendorDB.Vcsale.dbo.SaleBlock WHERE SaleBlockID = a.SaleBlockID)

--update rows exist

UPDATE a SET a.GameID = b.GameID,

		a.Amount = b.Amount,

		a.ChargePercent = b.ChargePercent

	FROM SaleBlock a

INNER JOIN VendorDB.Vcsale.dbo.SaleBlock b

	ON a.SaleBlockID = b.SaleBlockID

		AND CHECKSUM(a.GameID,a.Amount,a.ChargePercent) != CHECKSUM(b.GameID,b.Amount,b.ChargePercent)

--insert new rows

INSERT SaleBlock 

	SELECT a.* FROM VendorDB.Vcsale.dbo.SaleBlock a

LEFT JOIN SaleBlock b

	ON a.SaleBlockID = b.SaleBlockID

	WHERE b.SaleBlockID IS NULL

/*end update saleblock*/



/*update customer*/

--write new customer to vendor db

INSERT VendorDB.Vcsale.dbo.Customer

	SELECT a.* FROM Customer a

		--WHERE a.CustomerID > @maxCustomerID

		--AND NOT EXISTS(SELECT 1 FROM FROM VendorDB.Vcsale.dbo WHERE CustomerID = a.CustomerID)

LEFT JOIN VendorDB.Vcsale.dbo.Customer b

	ON a.CustomerID = b.CustomerID

	WHERE b.CustomerID IS NULL

		AND a.CustomerID > @maxCustomerID

--update customer status for sale

UPDATE a SET	a.ClassID = b.ClassID,

		a.Consume = b.Consume

	FROM Customer a

INNER JOIN VendorDB.Vcsale.dbo.Customer b

	ON a.CustomerID = b.CustomerID

		AND a.MailAccount = b.MailAccount

		AND CHECKSUM(a.ClassID,a.Consume) != CHECKSUM(b.ClassID,b.Consume)

--update customer profile for vendor

UPDATE a SET a.Phone = b.Phone,

		a.TrueName = b.TrueName,

		a.Non_US_Phone = b.Non_US_Phone,

		a.Country = b.Country,

		a.Area = b.Area

	FROM VendorDB.Vcsale.dbo.Customer a

INNER JOIN Customer b

	ON a.CustomerID = b.CustomerID

		AND a.MailAccount = b.MailAccount

		AND CHECKSUM(a.Phone,a.TrueName,a.Non_US_Phone,a.Country,a.Area)

			!=

			CHECKSUM(b.Phone,b.TrueName,b.Non_US_Phone,b.Country,b.Area)

/*end update customer*/



/*update saleorder*/

--write new saleorder to vendor db

INSERT VendorDB.Vcsale.dbo.SaleOrder

	SELECT a.* FROM SaleOrder a

LEFT JOIN VendorDB.Vcsale.dbo.SaleOrder b

	ON a.OrderID = b.OrderID AND a.OrderSerial = b.OrderSerial

	WHERE b.OrderID IS NULL

		AND a.OrderID > @maxSaleOrderID

------

INSERT VendorDB.Vcsale.dbo.SaleOrderItem

	SELECT a.* FROM SaleOrderItem a

LEFT JOIN VendorDB.Vcsale.dbo.SaleOrderItem b

	ON a.SaleOrderItemID = b.SaleOrderItemID

	WHERE b.SaleOrderItemID IS NULL

		AND a.SaleOrderItemID > @maxSaleOrderItemID

--update sale order status for sale

UPDATE a SET a.PayTime = b.PayTime,

		a.Status = b.Status

	FROM SaleOrder a

INNER JOIN VendorDB.Vcsale.dbo.SaleOrder b

	ON a.OrderID=b.OrderID AND a.OrderSerial = b.OrderSerial

	AND CHECKSUM(a.PayTime,a.Status) != CHECKSUM(b.PayTime,b.Status)

	WHERE a.OrderID > @maxSaleOrderID

------

UPDATE a SET a.LeaveAmount = b.LeaveAmount,

		a.Status = b.Status

	FROM SaleOrderItem a

INNER JOIN VendorDB.Vcsale.dbo.SaleOrderItem b

	ON a.SaleOrderItemID = b.SaleOrderItemID

		AND a.OrderID = b.OrderID

		AND CHECKSUM(a.LeaveAmount,a.Status) != CHECKSUM(b.LeaveAmount,b.Status)

--update sale order item tradetime for vendor

UPDATE a SET a.TradeTime = b.TradeTime

	FROM VendorDB.Vcsale.dbo.SaleOrderItem a

INNER JOIN SaleOrderItem b

	ON a.SaleOrderItemID = b.SaleOrderItemID

		AND a.OrderID = b.OrderID

	WHERE a.OrderID>@maxSaleOrderID

/*end update saleorder*/





GO

/*drop romate vendor database server*/

EXEC sp_dropserver 'VendorDB'

GO