--Скрип для пересоздания таблицы TPARENT_CHANNEL_PARA METER_LINKS dele

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
--Скрип для пересоздания таблицы TPARENT_CHANNEL_PARAMETER_LINKS
delete from TPARENT_CHANNEL_PARAMETER_LINKS
CREATE TABLE #TEMPTABLE
(
CHANNEL_SETTING_ID UNIQUEIDENTIFIER,
PARAMETER_SETTING_ID UNIQUEIDENTIFIER
)
DECLARE @CHANNEL_SETTING_ID UNIQUEIDENTIFIER
DECLARE @PARAMETER_SETTING_ID UNIQUEIDENTIFIER
DECLARE PARAMETERS_CURSOR CURSOR FOR SELECT CHANNEL_SETTING_ID, PARAMETER_SETTING_ID FROM TCHANNEL_PARAMETER_LINKS
OPEN PARAMETERS_CURSOR
FETCH NEXT FROM PARAMETERS_CURSOR INTO @CHANNEL_SETTING_ID, @PARAMETER_SETTING_ID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT into #TEMPTABLE
select @CHANNEL_SETTING_ID, @PARAMETER_SETTING_ID
;
WITH PARAMETERS (PARAMETER_SETTING_ID, DST_PARAMETER_SETTING_ID)
AS
(
SELECT PS.PARAMETER_SETTING_ID, PML.DST_PARAMETER_SETTING_ID
FROM TPARAMETER_SETTINGS PS
INNER JOIN TPARAMS_IN_EM_LINKS PML ON PML.SRC_PARAMETER_SETTING_ID = PS.PARAMETER_SETTING_ID
WHERE PS.PARAMETER_SETTING_ID = @PARAMETER_SETTING_ID
UNION ALL
SELECT PS.PARAMETER_SETTING_ID, PML.DST_PARAMETER_SETTING_ID
FROM TPARAMETER_SETTINGS PS
INNER JOIN TPARAMS_IN_EM_LINKS PML ON PML.SRC_PARAMETER_SETTING_ID = PS.PARAMETER_SETTING_ID
INNER JOIN PARAMETERS AS D ON PS.PARAMETER_SETTING_ID = D.DST_PARAMETER_SETTING_ID
)
insert into #TEMPTABLE select @CHANNEL_SETTING_ID, DST_PARAMETER_SETTING_ID from PARAMETERS
;
WITH PARAMETERS (PARAMETER_SETTING_ID, DST_PARAMETER_SETTING_ID)
AS
(
SELECT PS.PARAMETER_SETTING_ID, PML.DST_PARAMETER_SETTING_ID
FROM TPARAMETER_SETTINGS PS
INNER JOIN TPARAMS_IN_MM_LINKS PML ON PML.SRC_PARAMETER_SETTING_ID = PS.PARAMETER_SETTING_ID
WHERE PS.PARAMETER_SETTING_ID = @PARAMETER_SETTING_ID
UNION ALL
SELECT PS.PARAMETER_SETTING_ID, PML.DST_PARAMETER_SETTING_ID
FROM TPARAMETER_SETTINGS PS
INNER JOIN TPARAMS_IN_MM_LINKS PML ON PML.SRC_PARAMETER_SETTING_ID = PS.PARAMETER_SETTING_ID
INNER JOIN PARAMETERS AS D ON PS.PARAMETER_SETTING_ID = D.DST_PARAMETER_SETTING_ID
)
insert into #TEMPTABLE select @CHANNEL_SETTING_ID, DST_PARAMETER_SETTING_ID from PARAMETERS
;
FETCH NEXT FROM PARAMETERS_CURSOR INTO @CHANNEL_SETTING_ID, @PARAMETER_SETTING_ID
END
insert into TPARENT_CHANNEL_PARAMETER_LINKS select * from #TEMPTABLE
drop table #TEMPTABLE
CLOSE PARAMETERS_CURSOR
DEALLOCATE PARAMETERS_CURSOR