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
|
CREATE PROCEDURE [dbo].[achievement] AS
DECLARE @colonne varchar(64), @CREATE varchar(max), @dataType varchar(32), @length varchar(32), @INSERT varchar(max), @TABLE varchar(32)
SET @TABLE = 'OptinsCommerciaux'
DECLARE curseurVariables CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE
OPEN curseurVariables
FETCH curseurVariables INTO @colonne
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @colonne
IF @colonne = 'email' BEGIN FETCH curseurVariables INTO @colonne PRINT @colonne END
SET @CREATE = 'CREATE TABLE TMP (EMAIL varchar(64), colonneCourante '
SET @dataType = (SELECT data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test' AND column_name = @colonne)
SET @length = (SELECT character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test' AND column_name = @colonne)
IF (@dataType = 'varchar')
BEGIN SET @CREATE = @CREATE + @dataType + '(' + @length + '))' END
ELSE BEGIN SET @CREATE = @CREATE + @dataType + ')' END
PRINT ( @CREATE )
EXEC ( @CREATE )
SET @INSERT = 'INSERT INTO Tmp (email,colonneCourante) select ' + @TABLE + '.email, ' + @TABLE + '.[' + @colonne + '] from ' + @TABLE + ' INNER JOIN test on test.email = ' + @TABLE + '.email ' + 'where test.[' + @colonne + '] is null'
PRINT ( @INSERT )
EXEC ( @INSERT )
DECLARE @TraitementMail VARCHAR(50), @TraitementColonne VARCHAR(50), @SQL VARCHAR(max), @DEL VARCHAR(max)
WHILE EXISTS (SELECT TOP 1 Email FROM Tmp)
BEGIN
SET @TraitementMail = (SELECT TOP 1 Email FROM Tmp)
SET @TraitementColonne = (SELECT TOP 1 colonneCourante FROM Tmp)
SET @SQL = 'UPDATE test SET test.[' + @colonne + '] = ''' + @TraitementColonne + ''' WHERE Email = ''' + @TraitementMail + ''';'
PRINT (@SQL)
EXEC (@SQL)
SET @DEL = 'DELETE FROM Tmp WHERE Email = ''' + @TraitementMail + ''';'
PRINT (@DEL)
EXEC (@DEL)
END
DROP TABLE Tmp
FETCH curseurVariables INTO @colonne
END |
Partager