CREATE PROCEDURE P_RETRO_INSERTS
   @SCHEMA_NAME    sysname,
   @TABLE_NAME     sysname,
   @NO_IDENTITY    BIT = 1,
   @NO_CALC        BIT = 1,
   @FORCE_IDENTITY BIT = 0
AS
/******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2014-03-18 *
*******************************************************************************
* Procedure de génération de scripts SQL de rétro insertion des données d'une *
* table.                                                                      *
* PARAMETRES :                                                                *
*    @SCHEMA_NAME    nom du schema de la table                                *
*    @TABLE_NAME     nom de la table                                          *
*    @NO_IDENTITY    prise en compte colonne IDENTITY si vaut 0               *
*    @NO_CALC        prise en compte des colonnes calculées si vaut 0         *
*    @FORCE_IDENTITY forcement des valeurs IDENTITY. Si 1, les flags SET      *
*                    IDENTITY_INSERT ON et OFF sont rajoutés en début et fin  *
*                    du script SQL généré                                     *
*******************************************************************************
* Exemple :                                                                   *
     EXEC dbo.P_RETRO_INSERTS 'dbo', 'Personne';
* Pour générer les données de toutes les tables avec forçage d'IDENTITY,      *
* lancer la requête suivante :                                                *
     SELECT 'EXEC dbo.P_RETRO_INSERTS ''' + TABLE_SCHEMA +''', '''
                                          + TABLE_NAME +''', 0, 1, 1'
     FROM   INFORMATION_SCHEMA.TABLES
     WHERE  TABLE_TYPE = 'BASE TABLE'
* et exécutez le résultat de cette requête                                    *
******************************************************************************/
BEGIN
   SET NOCOUNT ON;
-- réajustement des paramètres par défaut  
   SELECT @NO_IDENTITY    = COALESCE(@NO_IDENTITY, 1),
          @NO_CALC        = COALESCE(@NO_CALC, 1),
          @FORCE_IDENTITY = COALESCE(@FORCE_IDENTITY, 0);
-- pas besoin de forcer un SET INDENTITY INSERT si pas d'IDENTITY          
   IF @FORCE_IDENTITY = 1
      IF NOT EXISTS(SELECT *
                    FROM   sys.COLUMNS AS c
                           INNER JOIN sys.objects AS o
                                 ON c.object_id = o.object_id
                           INNER JOIN sys.schemas AS s
                                 ON o.schema_id = s.schema_id
                    WHERE  s.name = @SCHEMA_NAME
                      AND  o.name = @TABLE_NAME
                      AND is_identity = 1)
         SET @FORCE_IDENTITY = 0;          
-- table de métadonnées  
   CREATE TABLE #METACOLS
    (ORDINAL      INT,
     IS_CHAR      BIT,
     IS_TEXT         BIT,
     IS_DATETIME  BIT,
     IS_BINARY    BIT,
     IS_GEO       BIT,
     COLUMN_NAME  sysname);
-- vérification d'existence de la table passée en argument
    IF NOT EXISTS (SELECT *
                   FROM   INFORMATION_SCHEMA.TABLES
                   WHERE  TABLE_NAME   = @TABLE_NAME
                     AND  TABLE_SCHEMA = @SCHEMA_NAME
                     AND  TABLE_TYPE   = 'BASE TABLE')
    BEGIN
        RAISERROR('La table %s.%s n''existe pas', 16, 1, @SCHEMA_NAME, @TABLE_NAME);
        RETURN;
    END
-- alimentation de la table de métadonnées des colonnes
    INSERT INTO #METACOLS (ORDINAL, IS_CHAR, IS_TEXT, IS_DATETIME, IS_BINARY, IS_GEO, COLUMN_NAME)
    SELECT ORDINAL_POSITION,
           CASE WHEN DATA_TYPE LIKE '%char%'  
                THEN 1
                ELSE 0 END,
          CASE WHEN DATA_TYPE LIKE '%text%'
                THEN 1
                ELSE 0 END,
          CASE WHEN DATA_TYPE = 'datetime'
                THEN 1
                ELSE 0 END,
          CASE WHEN DATA_TYPE LIKE '%binary%'
                 OR DATA_TYPE = 'image'  
                THEN 1
                ELSE 0 END,
          CASE WHEN DATA_TYPE LIKE '%geo%'
                THEN 1
                ELSE 0 END,
            COLUMN_NAME
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_NAME = @TABLE_NAME
      AND  TABLE_SCHEMA = @SCHEMA_NAME
-- cas particulier pour les colonnes calculées ou IDENTITY  
      AND  EXISTS(SELECT *
                 FROM   sys.COLUMNS AS c
                        INNER JOIN sys.objects AS o
                              ON c.object_id = o.object_id
                        INNER JOIN sys.schemas AS s
                              ON o.schema_id = s.schema_id
                 WHERE  c.name = COLUMN_NAME
                   AND  s.name = @SCHEMA_NAME
                   AND  o.name = @TABLE_NAME
                   AND  CASE WHEN is_identity = 1 AND @NO_IDENTITY = 1 THEN 0
                             WHEN is_computed = 1 AND @NO_CALC = 1 THEN 0
                             ELSE 1
                        END = 1);
-- variables locales pour la restitution des données  
   DECLARE @INSERT_LISTE NVARCHAR(MAX),
           @INSERT_DATA  NVARCHAR(MAX),
           @TABLE_DATA   NVARCHAR(261);
   SET @TABLE_DATA = '"--' + @SCHEMA_NAME + '.' + @TABLE_NAME +'"';
-- entête de la commande SELECT
    SET @INSERT_LISTE = N'SELECT ''INSERT INTO ' + @SCHEMA_NAME + '.' + @TABLE_NAME + N' (';
    SET @INSERT_DATA  = N' ';
-- partie liste des colonnes
    SELECT @INSERT_LISTE = @INSERT_LISTE + COLUMN_NAME + ','
    FROM   #METACOLS
    ORDER  BY ORDINAL;
-- partie listes des valeurs
    SELECT @INSERT_DATA =   @INSERT_DATA
            + N' CASE WHEN ' + COLUMN_NAME + N' IS NULL '
            + N' THEN ''NULL'' '
            + N' ELSE '
            +     CASE WHEN IS_CHAR = 1
                       THEN  N''''''''' + REPLACE(RTRIM(' + COLUMN_NAME + N'), '''''''', '''''''''''') + '''''''''
                   WHEN IS_DATETIME = 1
                       THEN  N''''''''' + CONVERT(CHAR(23),' + COLUMN_NAME + N', 121) + '''''''''                      
                      WHEN IS_TEXT = 1
                       THEN  N''''''''' + REPLACE(RTRIM(CAST(' + COLUMN_NAME + N' AS VARCHAR(max))), '''''''', '''''''''''') + '''''''''
                   WHEN IS_BINARY = 1
                       THEN  N'CONVERT(varchar(max),' + COLUMN_NAME + N', 2) + '''''''''    
                   WHEN IS_GEO = 1
                       THEN  N''''''''' + ' + COLUMN_NAME + N'.STAsText() + '''''''''                                      
                       ELSE N'CAST(' + COLUMN_NAME + N' AS VARCHAR(128))'
                  END
            + N' END + '','' + '
    FROM    #METACOLS
    ORDER BY ORDINAL;
-- ajustement des listes   
    SELECT @INSERT_LISTE = LEFT(@INSERT_LISTE,LEN(@INSERT_LISTE)-1) + N') VALUES ('' + ';
    SELECT @INSERT_DATA = LEFT(@INSERT_DATA,LEN(@INSERT_DATA)-8) + N' + '');'' AS ' + @TABLE_DATA + ' FROM ' + @SCHEMA_NAME + '.'  + @TABLE_NAME;
-- exécution de la requête
   IF @FORCE_IDENTITY = 1
   BEGIN
      SET @INSERT_LISTE = 'SELECT ''SET IDENTITY_INSERT ' + @SCHEMA_NAME + '.'  + @TABLE_NAME +' ON;'' AS ' + @TABLE_DATA + ' UNION ALL ' + @INSERT_LISTE;
      SET @INSERT_DATA = @INSERT_DATA + ' AS ' + @TABLE_DATA + ' UNION ALL SELECT ''SET IDENTITY_INSERT ' + @SCHEMA_NAME + '.'  + @TABLE_NAME +' OFF;''' ;      
   END;  
   EXEC (@INSERT_LISTE + @INSERT_DATA);
-- suppression de la table de métadonnées
    DROP TABLE #METACOLS;
END
GO