Code: Select all
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Сервер 2
Code: Select all
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
Сервер 1 добавлен как линкед сервер на Сервере 2
На Сервере 1 выполняем скрипт
Code: Select all
SET NOCOUNT ON
CREATE TABLE [tbl_test] (
[code1] [char] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
CONSTRAINT [PK_tbl_test] PRIMARY KEY CLUSTERED
(
[code1]
)
)
GO
CREATE TABLE [tbl_test2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[code1] [char] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[CodeType] [int] NOT NULL ,
CONSTRAINT [PK_tbl_test2] PRIMARY KEY CLUSTERED
(
[ID]
)
)
GO
INSERT INTO [tbl_test] ([code1])
SELECT '_RYH0KNUR1'
UNION
SELECT '_0LR0VS8Q3'
UNION
SELECT '_0LR0X01W1'
UNION
SELECT '_S9lLKW56T'
GO
INSERT INTO [tbl_test2] ( Code1, CodeType)
SELECT ' ', 3
UNION
SELECT '_RYH0KNUR1', 3
UNION
SELECT '_0LR0VS8Q3', 3
UNION
SELECT '_0LR0X01W1', 3
UNION
SELECT '_S9lLKW56T', 3
GO
SELECT *
FROM tbl_test2 f
INNER JOIN tbl_test m ON m.Code1 = f.Code1
WHERE f.CodeType <> 4
UPDATE f
SET CodeType = 4
FROM tbl_test2 f
INNER JOIN tbl_test m ON m.Code1 = f.Code1
WHERE f.CodeType <> 4
SELECT * FROM tbl_test2
GO
-- Возвращаем в исходное состояние
UPDATE tbl_test2
SET CodeType = 3
GO
Результат
Code: Select all
ID code1 CodeType code1
----------- ---------- ----------- ----------
2 _0LR0VS8Q3 3 _0LR0VS8Q3
3 _0LR0X01W1 3 _0LR0X01W1
4 _RYH0KNUR1 3 _RYH0KNUR1
5 _S9lLKW56T 3 _S9lLKW56T
ID code1 CodeType
----------- ---------- -----------
1 3
2 _0LR0VS8Q3 4
3 _0LR0X01W1 4
4 _RYH0KNUR1 4
5 _S9lLKW56T 4
Теперь запускаем с Сервера 2
Code: Select all
SELECT *
FROM <servername>.<dbname>.<owner>.tbl_test2 f
INNER JOIN <servername>.<dbname>.<owner>.tbl_test m ON m.Code1 = f.Code1
WHERE f.CodeType <> 4
UPDATE f
SET CodeType = 4
FROM <servername>.<dbname>.<owner>.tbl_test2 f
INNER JOIN <servername>.<dbname>.<owner>.tbl_test m ON m.Code1 = f.Code1
WHERE f.CodeType <> 4
SELECT * FROM <servername>.<dbname>.<owner>.tbl_test2
Результат
Code: Select all
ID code1 CodeType code1
----------- ---------- ----------- ----------
2 _0LR0VS8Q3 3 _0LR0VS8Q3
3 _0LR0X01W1 3 _0LR0X01W1
4 _RYH0KNUR1 3 _RYH0KNUR1
5 _S9lLKW56T 3 _S9lLKW56T
ID code1 CodeType
----------- ---------- -----------
1 4
2 _0LR0VS8Q3 4
3 _0LR0X01W1 4
4 _RYH0KNUR1 4
5 _S9lLKW56T 4
Поле CodeType стало равным 4 во всех строках таблицы
