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;
*/ |