Génération d’un « rétro script » d’insertion

Il est souvent intéressant de générer les commande « INSERT » relatives aux lignes d’une table existante, ceci par exemple afin de procéder à des essais, des tests ou encore pour illustrer des posts sur developpez.com ou d’autres forums afin de faciliter ceux qui tente de vous répondre avec de vraies données. La procédure qui est publiée ci-dessous permet de générer ces lignes d’insertion depuis n’importe quelle base de votre instance SQL Server.

Le principe est simple : faire générer par SQL Server des scripts SQL de rétro-insertion des données, sous forme d’ordre SQL de type INSERT INTO.
Le format de sortie est du texte UNICODE explicite sauf les « LOBs » pour lesquels les valeurs sont sous forme binaires (varchar(max), nvarchar(max), varbinary(max), text, ntext, image, xml, geometry, geography, sql_variant, hierarchyid…).

PARAMÈTRES

@SCH et @TBL sont les paramètres devant faire référence au schéma SQL de la table (par défaut dbo) et au nom de la table elle même.

Différents paramètres permettent de régler les problématiques suivantes :

Nombre de lignes :

  • @LIMIT : valeurs stricte entière limitant le nombre de lignes (doit être NULL ou > 0). par défaut = 1000.
  • @TABLESAMPLE_PC : échantillon approximatif et aléatoire en nombre de ligne pour une grande tables.
  • Les deux pouvant être combinés.

    Prise en compte de certaines colonnes :

  • @KEEP_ID : si 1 alors il y a conservation des auto incréments.
  • @KEEP_LOBS : si 1 alors il y a conservation des « LOBs » dont les valeurs sont générées sous forme binaire (hexadécimale).
  • Les deux pouvant être spécifiées

    Clauses de la requête :

  • @WHERE : contenu de la clause WHERE de restriction des lignes, telle que pouvant figurer dans une requête SELECT * sur la table.
  • @ORDER_BY : contenu de la clause ORDER BY de tri des lignes, telle que pouvant figurer dans une requête SELECT * sur la table.
  • @ALEA : si 1, tri aléatoire des données de la table
  • Les clauses WHERE et ORDER BY peuvent être spécifiée toutes les deux, mais vous ne pouvez pas spécifier simultanément un contenu pour le paramètre @ORDER_BY et une valeur de 1 pour le paramètre @ALEA, il faut choisir soit un tri aléatoire soit un tri spécifique.

    Paramètres d’exécution :

  • @NOLOCK : lite les données sans se préoccuper du verrouillage (idéal pour les jeux de données)
  • @DEBUG : si 1, renvoi des jeux de résultats intermédiaires pour le débogage
  • Les deux pouvant êtres spécifiés.

    LA PROCÉDURE

    CREATE PROCEDURE dbo.sp__GENERATE_INSERT
       @SCH              sysname = 'dbo',   -- schema de la table visée
       @TBL              sysname,           -- nom de la table visée
       @KEEP_ID          BIT = 1,           -- conservation des auto incréments
       @KEEP_LOBS        BIT = 0,           -- ne pas prendre en compte les "LOBs"
       @LIMIT            INT = 1000,        -- limite en nombre de lignes
       @TABLESAMPLE_PC   FLOAT = NULL,      -- échantillon en pourcentage
       @WHERE            NVARCHAR(MAX) = '',-- clause WHERE de la requête
       @ORDER_BY         NVARCHAR(MAX) = '',-- clause ORDER BY de la requête
       @ALEA             BIT = 0,           -- valeur dans un ordre aléatoire
       @NOLOCK           BIT = 1,           -- lecture sale
       @DEBUG            BIT = 0            -- informations de débogage
    AS
    /******************************************************************************
    * GÉNÉRATION D'INSERT SQL                                                     *
    * Procédure générant un rétro-script d'insertion de lignes d'une table        *
    *******************************************************************************  
    * Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com *
    * Plus d'info. sur http://sqlpro.developpez.com   -  2017-01-11 - version 1.0 *  
    *******************************************************************************  
    * Cette procédure prend en argument le nom d'une table et son schéma SQL,     *
    * ainsi que divers paramètres de limitation des colonnes et des lignes,       *
    * et ordre des données ainsi que des paramètres d'exécution                   *
    * LIMITATION : ne prends en compte que les tables ou vues utilisateur         *
    *******************************************************************************
    * ATTENTION  : procédure système ! Exécutable depuis n'importe quelle base    *
    *                                                                             *
    * Paramètre en entrée : mot partiel dont on cherche au moins une racine       *
    *   @SCH              sysname         schema SQL de la table (dbo par défaut) *
    *   @TBL              sysname,        nom de la table visée par le script     *
    *   @LIMIT            INT             limite du nombre de lignes retournées   *
    *   @KEEP_ID          BIT             si 1 conserve la colonne IDENTITY       *
    *   @ALEA             BIT             si 1, valeurs dans un ordre aléatoire   *
    *   @TABLESAMPLE_PC   FLOAT           échantillon approximatif en pourcentage *
    *   @ORDER_BY         NVARCHAR(max)   clause ORDER BY de la requête           *
    *   @WHERE            NVARCHAR(max)   clause WHERE de la requête              *
    *   @NOLOCK           BIT             lecture sale (ignore les verrous)       *
    *   @KEEP_LOBS        BIT             si 0 ne pas prendre en compte les "LOBs"*
    *   @DEBUG            BIT             si 1 ajouter information de débogage    *
    *                                                                             *
    * NOTA : seule le paramètre TBL est obligatoire                               *
    *                                                                             *
    * EXEMPLE :                                                                   *
    * EXEC dbo.sp__GENERATE_INSERT                                                *
    *    @SCH              = 'dbo',                                               *
    *    @TBL              = 'customers',                                         *
    *    @LIMIT            = 100,                                                 *
    *    @KEEP_ID          = 0,                                                   *
    *    @ALEA             = 1,                                                   *
    *    @TABLESAMPLE_PC   = NULL,                                                *
    *    @ORDER_BY         = NULL,                                                *
    *    @WHERE            = 'date_create > ''2016-01-01''',                      *
    *    @NOLOCK           = 1,                                                   *
    *    @KEEP_LOBS        = 0,                                                   *
    *    @DEBUG            = 0                                                    *
    * Résultats :                                                                 *
    *    INSERT INTO dbo.customers ( .... ) VALUES ( ... );                       *
    *    INSERT INTO dbo.customers ( .... ) VALUES ( ... );                       *
    *    INSERT INTO dbo.customers ( .... ) VALUES ( ... );                       *
    *    ...                                                                      *

    ******************************************************************************/

    BEGIN

    SET NOCOUNT ON;

    DECLARE @IS_VIEW        BIT,             -- la table est une vue
            @HAS_IDENTITY   BIT,             -- table avec colonne IDENTITY
            @OID            INT,             -- object_id de la table
            @COL_IDENTITY   NVARCHAR(128),   -- nom colonne IDENTITY  
            @COLS           NVARCHAR(MAX),   -- liste des colonnes de la table
            @SQL            NVARCHAR(MAX),   -- requête SQL dynamique
            @O_NAME         NVARCHAR(261),   -- nom complet de la table
            @ROWCOUNT       BIGINT;          -- nombre de lignes traitées

    --=============================================================================
    -- TEST TRIVIAUX :
    --=============================================================================
    IF @LIMIT IS NOT NULL AND @LIMIT  0 ou NULL.',
                 16, 1, @LIMIT);
       RETURN;
    END;
    IF @TABLESAMPLE_PC IS NOT NULL AND
       (@TABLESAMPLE_PC  100)
    BEGIN
       DECLARE @TPC VARCHAR(32) = CAST(@TABLESAMPLE_PC AS VARCHAR(32));
       RAISERROR('
    Valeur @TABLESAMPLE_PC (%s) incorrecte. Valeurs possibles ] 0 .. 100 ] ou NULL.',
                 16, 1, @TPC);
       RETURN;
    END;
    IF @ALEA = 1 AND @ORDER_BY IS NOT NULL
    BEGIN
       RAISERROR('
    Valeurs contradictoires : soit (@ALEA = 1), soit (@ORDER_BY = %s).',
                 16, 1, @ORDER_BY);
       RETURN;
    END;

    --=============================================================================
    -- TEST FONCTIONNELS :
    --=============================================================================

    -- est-ce une vue ?
    SELECT @IS_VIEW = CASE WHEN TABLE_TYPE = '
    VIEW' THEN 1 ELSE 0 END
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_SCHEMA = @SCH AND TABLE_NAME = @TBL;

    -- la table ou vue existe-t-elle ?
    IF @@ROWCOUNT = 0
    BEGIN
       RAISERROR('
    La TABLE ou vue %s.%s n''existe pas dans la base courante.',
                 16, 1, @SCH, @TBL);
       RETURN;
    END;

    -- si c'
    est une vue, pas de SAMPLE !
    IF @IS_VIEW = 1 AND @TABLESAMPLE_PC IS NOT NULL
    BEGIN
       RAISERROR('L''objet %s.%s est une vue. @TABLESAMPLE_PC impossible.',
                 16, 1, @SCH, @TBL);
       RETURN;
    END;

    SELECT @ORDER_BY = NULLIF(@ORDER_BY, ''),
           @WHERE = NULLIF(@WHERE, '');

    -- la clause ORDER BY est-elle valide ?
    BEGIN TRY
       SET @SQL = N'SELECT TOP(0) * FROM ' + @O_NAME + ' ORDER BY ' + @ORDER_BY;
       EXEC (@SQL);
    END TRY  
    BEGIN CATCH
       RAISERROR('Paramètre ORDER BY (%s) invalide pour la table %s.',
                 16, 1, @ORDER_BY, @O_NAME);
       RETURN;
    END CATCH;

    -- la clause WHERE est-elle valide ?
    BEGIN TRY
       SET @SQL = N'SELECT TOP(1) * FROM ' + @O_NAME + ' WHERE ' + @WHERE;
       EXEC (@SQL);
    END TRY  
    BEGIN CATCH
       RAISERROR('La clause WHERE (%s) passée en paramètre pour la table %s n''est pas valide.',
                 16, 1, @WHERE, @O_NAME);
       RETURN;
    END CATCH;


    --=============================================================================
    -- RÉCUPÉRATION DE VALEURS UTILES
    --=============================================================================

    -- récupération de son object_id et formation du nom complet:
    SELECT @OID = OBJECT_ID(@SCH+'.'+@TBL),
           @O_NAME = N'[' + @SCH + '].[' + @TBL + ']';

    IF @DEBUG = 1 SELECT CONCAT(@O_NAME, ' : ', @OID) AS NOM_TABLE;

    -- as t-elle une colonne IDENTITY ?
    SET @HAS_IDENTITY = CAST(OBJECTPROPERTYEX(@OID, 'TableHasIdentity') AS BIT);

    -- Si pas ,IDENTITY alors, pas de KEEP_ID
    IF @HAS_IDENTITY = 0
       SET @KEEP_ID = 0;

    -- quelle est la colonne IDENTITY
    IF @HAS_IDENTITY = 1
       SELECT @COL_IDENTITY = name
       FROM   sys.COLUMNS
       WHERE  object_id = @OID
         AND  is_identity = 1;

    IF @DEBUG = 1 SELECT CONCAT('HAS_IDENTITY ? ' , @HAS_IDENTITY, ' : ',  @COL_IDENTITY);

    -- paramétrage de conversion des types en caractères
    DECLARE @TYPES TABLE
       (sys_type_name sysname, type_familly VARCHAR(16), transform VARCHAR(64));
    INSERT INTO @TYPES
    SELECT name,
           CASE WHEN name LIKE '%char' OR name = 'sysname'
                   THEN 'CHAR'
                WHEN name IN ('float', 'real')
                   THEN 'REAL'
                WHEN name IN ('decimal', 'numeric', 'money', 'smallmoney')
                   THEN 'DEC'  
                WHEN name IN ('tinyint', 'smallint', 'int', 'bigint')
                   THEN 'INT'  
                WHEN name IN ('binary', 'varbinary')
                   THEN 'BIN'
                WHEN name IN ('date', 'time', 'datetime', 'datetime2')
                   THEN 'DATIME'
                WHEN name = 'uniqueidentifier' THEN
                   'GUID'
                WHEN name = 'bit' THEN
                   'BIT'
                WHEN name = 'datetimeoffset' THEN
                   'DTZ'
                ELSE 'LOB'
           END,
           CASE WHEN name LIKE '%varchar' OR name = 'sysname'
                   THEN ''''''''' + REPLACE(#VAL, '''''''', '''''''''''') + '''''''''
                WHEN name LIKE '%char' OR name = 'sysname'
                   THEN ''''''''' + REPLACE(RTRIM(#VAL), '''''''', '''''''''''') + '''''''''
                WHEN name IN ('float', 'real', 'decimal', 'numeric',
                              'money', 'smallmoney',
                              'tinyint', 'smallint', 'int', 'bigint',
                              'uniqueidentifier', 'bit')
                   THEN 'CAST(#VAL AS VARCHAR(38))'
                WHEN name IN ('date', 'time', 'datetime',
                              'datetime2', 'datetimeoffset')
                   THEN ''''''''' + CONVERT(VARCHAR(36), #VAL, 127) + '''''''''
                ELSE 'CONVERT(varchar(max),#VAL,2)'
           END
    FROM sys.systypes;

    IF @DEBUG = 1 SELECT * FROM @TYPES;

    -- tables des colonnes et transformetion a effectuée pour la table a exporter
    DECLARE @COLUMNS TABLE
    (column_ordinal  INT,
     column_name      sysname,
     sys_type_name   sysname,
     type_familly    sysname,
     col_transform   VARCHAR(200));

    -- alimentation de la table des colonnes avec le paramétrage de conversion
    INSERT INTO @COLUMNS (column_ordinal, column_name, sys_type_name,
                          type_familly, col_transform)
    SELECT ROW_NUMBER() OVER(ORDER BY column_id),
           c.name,
           CASE c.max_length WHEN -1 THEN 'ntext' ELSE t.name END,
           type_familly,
           transform
    FROM   sys.COLUMNS AS c
           JOIN sys.types AS tu
                ON c.user_type_id = tu.user_type_id
           JOIN sys.types AS t
                ON tu.system_type_id = t.user_type_id
           JOIN @TYPES AS tp
                ON CASE c.max_length
                      WHEN -1 THEN 'ntext'
                      ELSE t.name
                   END =tp.sys_type_name
    WHERE  c.object_id = @OID
    AND    type_familly IN (SELECT type_familly
                            FROM   @TYPES
                            EXCEPT
                            SELECT 'LOB'
                            WHERE  @KEEP_LOBS = 0)
    AND    c.name  != CASE
                         WHEN @KEEP_ID = 0
                            THEN COALESCE(@COL_IDENTITY, '')
                         ELSE ''
                      END;

    IF @DEBUG = 1 SELECT * FROM @COLUMNS;

    -- obtention de la liste des colonnes
    SELECT @COLS = LTRIM(STUFF((SELECT ', ' + '[' + column_name + ']'
                                FROM   @COLUMNS
                                FOR XML PATH('')),1,1,''));

    IF @DEBUG = 1 SELECT @COLS AS COLONNES

    --=============================================================================
    -- RÉCUPÉRATION DES LIGNES
    --=============================================================================

    -- construction dynamique de la requête capturant les données
    SET @SQL = N'SELECT '
             + CASE WHEN (@ORDER_BY IS NULL OR @ORDER_BY = '')
                          AND @LIMIT IS NOT NULL
                      THEN N'TOP(' + CAST(@LIMIT AS NVARCHAR(32)) + N') '
                    ELSE N''
               END + N'IDENTITY(BIGINT, 1, 1) AS ___, ' + @COLS
             + N'INTO ##EXPORT_INSERT_53514C70726F '
             + N'FROM ' + @O_NAME + N' '
             + COALESCE(N'TABLESAMPLE ('
                        + CAST(@TABLESAMPLE_PC AS NVARCHAR(32))
                        + N' PERCENT) ', '')
             + COALESCE(N'WHERE ' + @WHERE + ' ', '')
             + CASE WHEN @ALEA = 1 THEN N'ORDER BY NEWID() '
                    WHEN @ORDER_BY IS NULL THEN ''
                    WHEN @LIMIT IS NULL THEN N'ORDER BY ' + @ORDER_BY
                    ELSE N'ORDER BY ' + @ORDER_BY + N'OFFSET 0 ROW FETCH NEXT '
                         + CAST(@LIMIT AS NVARCHAR(32)) + N' ROWS ONLY'
               END;


    IF @DEBUG = 1 SELECT @SQL AS "SQL";

    -- renvoie du texte de la requête dans l'onglet message
    PRINT REPLACE(
             REPLACE(@SQL, N'INTO ##EXPORT_INSERT_53514C70726F ', N''),
             'IDENTITY(BIGINT, 1, 1) AS ___, ', '') + ';';

    -- exécution de la requête dynamique de capture des données
    IF @NOLOCK = 1
       SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    ELSE    
       SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRY
       EXEC (@SQL);
       SET @ROWCOUNT = @@ROWCOUNT;
    END TRY
    BEGIN CATCH
       DROP TABLE ##EXPORT_INSERT_53514C70726F;
       THROW;
       RETURN;
    END CATCH;
    -- renvoie le nombre de ligne à exporter dans l'onglet message
    PRINT CAST(@ROWCOUNT AS VARCHAR(32)) + ' rows returned';

    -- construction de la requête générant le résultat
    IF @KEEP_ID = 1
       SELECT N'SET IDENTITY_INSERT ' + @O_NAME
                + ' ON;';  
    SET @SQL = N'';
    SET @SQL = @SQL +  N'SELECT ''INSERT INTO '
             + @O_NAME + N' (' + @COLS + N') VALUES ('' + '
    SELECT @SQL = @SQL + 'COALESCE(' +
           + REPLACE(col_transform,
                     N'#VAL',
                     N'[' + column_name + N']') + ', ''NULL'') + '', '' + '
    FROM   @COLUMNS
    ORDER  BY column_ordinal;
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 8)
             + N' + '');''  FROM ##EXPORT_INSERT_53514C70726F ORDER BY ___'

    IF @DEBUG = 1 SELECT @SQL AS "SQL";

    -- exécution de la requête générant les INSERTs
    EXEC (@SQL)

    IF @KEEP_ID = 1
       SELECT 'SET IDENTITY_INSERT ' + @O_NAME + ' OFF';
       
    -- suppression de la table temporaire
    DROP TABLE ##EXPORT_INSERT_53514C70726F;

    END;
    GO

    Cette procédure doit être marquée à titre de procédure système afin de pouvoir être utilisée depuis n’importe quelle base. Il faut, bien entendu l’avoir préalablement créée dans la base master. Ceci s’effectue à l’aide de la commande :

    EXEC sys.sp_MS_marksystemobject 'dbo.sp__GENERATE_INSERT';

    EXEMPLES :

    Voici quelques exemples d’exécution :

    USE mabase
    GO

    EXEC dbo.sp__GENERATE_INSERT
       @SCH              = 'dbo',       -- schema de la table visée
       @TBL              = 'customers', -- nom de la table visée
       @LIMIT            = 100,         -- limite en nombre de lignes
       @KEEP_ID          = 0,           -- conservation des auto incréments
       @ALEA             = 1,           -- valeur dans un ordre aléatoire
       @TABLESAMPLE_PC   = NULL,        -- échantillon en pourcentage
       @ORDER_BY         = NULL,        -- clause ORDER BY de la requête
       @WHERE            = NULL,        -- clause WHERE de la requête
       @NOLOCK           = 1,           -- lecture sale
       @KEEP_LOBS        = 0,           -- ne pas prendre en compte les "LOBs"
       @DEBUG            = 1            -- mode débogage activé
    GO

    EXEC dbo.sp__GENERATE_INSERT  @TBL= 'customers';
    GO

    LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE
    Le code !

    Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
    Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
    Moste  Valuable  Professionnal  Microsoft  SQL Server
    Société SQLspot  :  modélisation, conseil, formation,
    optimisation,  audit,  tuning,  administration  SGBDR
    Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

    L’entreprise SQL Spot
    Le site web sur le SQL et les SGBDR

    MVP Microsoft SQL
Server

    Développez et administrez pour la performance avec SQL Server 2014

    Développez et administrez pour la performance avec SQL Server 2014

    Laisser un commentaire