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
| CREATE TABLE [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville] nchar(10));
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('new-york');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('paris');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('bombay ');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('douala ');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('calcuta');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('rome');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('amsterdam');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('londres ');
INSERT INTO [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] ([ville]) VALUES('sydney');
DECLARE @SrcRecCount AS INTEGER;
DECLARE @SubstRecCount AS INTEGER;
DECLARE @CopiesOfFullSet AS INTEGER;
SELECT @SrcRecCount = COUNT(*) FROM [dbo].[Table_sir_test2];
SELECT @SubstRecCount = COUNT(*) FROM [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b];
SET @CopiesOfFullSet = @SrcRecCount / @SubstRecCount;
CREATE TABLE [#ShuffleRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] (Id int IDENTITY(1,1), [ville] nchar(10));
DECLARE @i AS INTEGER;
SET @i = 0;
WHILE @i < @CopiesOfFullSet
BEGIN
INSERT INTO [#ShuffleRecs65318373-70c0-46b0-9aa6-7e0a61886f7b]
SELECT [ville]
FROM [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b]
ORDER BY NEWID();
SET @i = @i + 1;
END
INSERT INTO [#ShuffleRecs65318373-70c0-46b0-9aa6-7e0a61886f7b]
SELECT TOP (@SrcRecCount - @SubstRecCount * @CopiesOfFullSet) [ville]
FROM [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b]
ORDER BY NEWID();
UPDATE src
SET src.[ville] = shuffled.[ville]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY [ville] DESC) AS 'RowNumber'
, *
FROM [dbo].[Table_sir_test2]) AS src
INNER JOIN [#ShuffleRecs65318373-70c0-46b0-9aa6-7e0a61886f7b] AS shuffled
ON shuffled.Id = src.RowNumber
-- Cleanup
DROP TABLE [#ShuffleRecs65318373-70c0-46b0-9aa6-7e0a61886f7b];
DROP TABLE [#SubstituteRecs65318373-70c0-46b0-9aa6-7e0a61886f7b]; |
Partager