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
60
61
62
63
64
65
66
|
/*Files\SQL\INSERT.SQL*/
DECLARE @cpt int = :pStart
DECLARE @cptD int
DECLARE @cptM int
DECLARE @LetterCode nvarchar(50)
DECLARE @IdentityD TABLE (ID int, LC nvarchar(50))
DECLARE @IdentityM TABLE (ID int, LC nvarchar(50))
WHILE @cpt <= :pEnd
BEGIN
SET @cptD = @cpt
SET @LetterCode = ''
WHILE @cptD > 0
BEGIN
SET @cptM = @cptD % 26
IF @cptM = 0
SET @cptM = 26
SET @cptD = (@cptD - 1) / 26
SET @LetterCode = Char(64 + @cptM) + @LetterCode
END
IF @cptD > 0
SET @LetterCode = Char(64 + @cptD) + @LetterCode
INSERT INTO XXX.Z_SP_MASTER_10K (M_NUMBER, M_TEXT, M_ROW) OUTPUT INSERTED.M_ID, INSERTED.M_TEXT INTO @IdentityM VALUES (@cpt, 'M_' + @LetterCode, @cpt)
INSERT INTO XXX.Z_SP_DETAIL_10K (D_NUMBER, D_TEXT, D_ROW) OUTPUT INSERTED.D_ID, INSERTED.D_TEXT INTO @IdentityD VALUES (@cpt, 'D_' + @LetterCode, @cpt)
SET @cpt = @cpt + 1
END
/*Ceci doit être utilisé en modifiant CommandTimeout, compter plus de 30s à partir de 2500x2500*/
INSERT INTO XXX.Z_SP_RELATION_10K (M_ID, D_ID, R_NUMBER, R_TEXT, R_ROW)
SELECT M.ID, D.ID, M.ID + D.ID, M.LC + '_' + D.LC, ROW_NUMBER() OVER(ORDER BY M.ID, D.ID)
FROM @IdentityM M
CROSS JOIN @IdentityD D
/* Equivalent du INSERT INTO CROSS JOIN ci-dessus
DECLARE RelationM CURSOR FOR
SELECT M.ID, M.LC
FROM @IdentityM M
ORDER BY M.ID ASC;
OPEN RelationM
FETCH NEXT FROM RelationM
INTO @cptM, @LetterCode
SET @cpt = 0
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO XXX.Z_SP_RELATION_10K (M_ID, D_ID, R_NUMBER, R_TEXT, R_ROW)
SELECT @cptM, D.ID, @cptM + D.ID, @LetterCode + '_' + D.LC, @cpt + ROW_NUMBER() OVER(ORDER BY D.ID ASC)
FROM @IdentityD D
SET @cpt = @cpt + @@RowCount
FETCH NEXT FROM RelationM
INTO @cptM, @LetterCode
END
CLOSE RelationM;
DEALLOCATE RelationM;
*/ |
[10.3.3]Convertir commande mssql
Répondre avec citation
Envoyé par mario9
Partager