October 13, 2015 at 2:25 pm
This might be very basic for some of you,
We are moving to NEW_Server and want to set up a process every weekly or monthly where table 'AF ' from OLD_Server gets updated from NEW_Server table SS, whenever there are any changes made into NEW_Server, table 'SS'. I have attached the code for sample data, for example dealershipCode 200 named got changed in the new table, dealershipCode 300, address got changed and there was another entry dealershipCode 400. I want all the changes to reflect on the OLD_Server table AF,
I want to set up a process where Table 'SS' updates Table 'AF', In the sample data its TEST TABLE 1 and TEST TABLE 2. both the server are added as linked servers to each other if that helps.
CREATE TABLE TEST_TABLE1
(
DealershipCode INT,
DealershipName varchar(40),
DealershipAddress varchar(30)
)
INSERT INTO TEST_TABLE1(DealershipCode, DealershipName, DealershipAddress)
Values (100, 'ABC Motors', '123 ABC Street TX')
INSERT INTO TEST_TABLE1(DealershipCode, DealershipName, DealershipAddress)
Values (200, '123 Motors', '456 ABC Street TX')
INSERT INTO TEST_TABLE1 (DealershipCode, DealershipName, DealershipAddress)
Values (300, 'XYZ Motors', '789 ABC Street TX')
Select * FROM TEST_TABLE1
--Create test table 2 (Server NEW, Table name 'SS')
CREATE TABLE TEST_TABLE2
(
DealershipCode INT,
DealershipName varchar(40),
DealershipAddress varchar(30)
)
INSERT INTO TEST_TABLE2(DealershipCode, DealershipName, DealershipAddress)
Values (100, 'ABC Motors', '123 ABC Street TX')
INSERT INTO TEST_TABLE2(DealershipCode, DealershipName, DealershipAddress)
Values (200, 'General Motors', '456 ABC Street TX')
INSERT INTO TEST_TABLE2 (DealershipCode, DealershipName, DealershipAddress)
Values (300, 'XYZ Motors', '789 New Street TX')
INSERT INTO TEST_TABLE2 (DealershipCode, DealershipName, DealershipAddress)
Values (400, 'Liquid Motors', '1112 ABC Street TX')
SELECT * FROM TEST_TABLE1
SELECT * FROM TEST_TABLE2
--DROP TABLE TEST_TABLE1
--DROP TABLE TEST_TABLE2
October 13, 2015 at 2:58 pm
Following seems to work but the TEST_TABLE1 and TEST_TABLE2 are on the same server and same database, not sure how do I do it within different servers and database:
MERGE INTO TEST_TABLE1
USING TEST_TABLE2
ON TEST_TABLE1.DealershipCode = TEST_TABLE2.DealershipCode
WHEN MATCHED THEN
UPDATE
SET DealershipName = TEST_TABLE2.DealershipName, DealershipAddress = TEST_TABLE2.DealershipAddress;
October 13, 2015 at 4:19 pm
so far I have the code below, I am trying to update TEST_TABLE1 from the table 'SRV' on the server 'New', not sure if I am using correct syntax.
It gives me this error: 'The multi-part identifier "New.SRV.dbo.TEST_TABLE2.DealershipCode" could not be bound."
MERGE INTO TEST_TABLE1
USING [NEW].[SRV].[dbo].[TEST_TABLE2]
ON TEST_TABLE1.DealershipCode = [NEW].[SRV].[dbo].[TEST_TABLE2].[DealershipCode]
WHEN MATCHED THEN
UPDATE
SET DealershipName = [NEW].[SRV].[dbo].[TEST_TABLE2].[DealershipName],
DealershipAddress = [NEW].[SRV].[dbo].[TEST_TABLE2].[DealershipAddress];
When I query just the following it works:
SELECT * FROM [NEW].[SRV].[dbo].[TEST_TABLE2]
October 14, 2015 at 8:39 am
Can anyone help???
October 14, 2015 at 9:59 am
You don't need MERGE, use UPDATE instead:
UPDATE tt1
SET DealershipName = TT2.[DealershipName],
DealershipAddress = TT2.[DealershipAddress]
FROM TEST_TABLE1 tt1
INNER JOIN TEST_TABLE2 tt2 ON TT2.DealershipCode = TT1.DealershipCode;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply