--===========================================================================--
-- GÉNÉRATION D'INSERT SQL                                                   --
--===========================================================================--
-- Fredéric Brouard alias SQLpro                http://sqlpro.developpez.com --
-- Société SQL SPOT - http://www.sqlspot.com        2019-04-22 - version 3.0 --
--===========================================================================--

--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- PHASE 1 : création de la procédure                                        --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--

CREATE OR ALTER 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 aléatoire 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,
   @ADD_BRACKET      BIT = 0,           -- pas de crochets
   @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"*
*   @ADD_BRACKET      BIT             si 0 pas de crochets dans le résultat   *
*   @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 ( ... );                       *
*    ...                                                                      *
*******************************************************************************
* MODIFICATIONS                                                               *
* 2017-09-02 : Correction de bugs                                             * 
* 2019-04-22 : ajout paramètre @ADD_BRACKET et correction bugs                *
******************************************************************************/
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
BEGIN
   RAISERROR('Valeur @LIMIT (%d) incorrecte. Valeurs possibles ] 0 .. 100 ] ou NULL.', 16, 1, @LIMIT);
   RETURN;
END;

IF @TABLESAMPLE_PC IS NOT NULL AND
   (@TABLESAMPLE_PC <=0 OR @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), mais pas les deux', 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. Le paramètre @TABLESAMPLE_PC n''est pas applicable.', 16, 1, @SCH, @TBL);
   RETURN;
END;

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

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

-- 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('Clause ORDER BY %s invalide pour la table %s.%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
--=============================================================================

IF @DEBUG = 1 
   RAISERROR('Table : %s - object_id : %d', 10, 1, @O_NAME,@OID) WITH NOWAIT;

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

DECLARE @HASID TINYINT = @HAS_IDENTITY;

IF @DEBUG = 1 
   RAISERROR('Identity ? %d - Column : %s', 10, 1, @HASID, @COL_IDENTITY) WITH NOWAIT;

-- 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 'CHR'
            WHEN name IN ('float', 'real')
               THEN 'FLT'
            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 'DTM'
            WHEN name = 'uniqueidentifier' THEN
               'UID'
            WHEN name = 'bit' THEN
               'BIT'
            ELSE 'LOB'
       END,
       CASE WHEN name LIKE '%char' OR name = 'sysname'
               THEN 'COALESCE('''''''' + REPLACE(#VAL, '''''''', '''''''''''') + '''''''', ''NULL'')'
            WHEN name IN ('float', 'real', 'decimal', 'numeric',
                          'money', 'smallmoney',
                          'tinyint', 'smallint', 'int', 'bigint',
                          'uniqueidentifier', 'bit')
               THEN 'COALESCE(CAST(#VAL AS VARCHAR(64)), ''NULL'')'
            WHEN name IN ('date', 'time', 'datetime',
                          'datetime2', 'datetimeoffset')
               THEN 'COALESCE('''''''' + CONVERT(VARCHAR(36), #VAL, 127) + '''''''', ''NULL'')'
            WHEN name LIKE '%binary'
               THEN 'COALESCE(CONVERT(NVARCHAR(max), #VAL, 1), ''NULL'')'
            ELSE 'COALESCE(CONVERT(Nvarchar(max), #VAL, 2), ''NULL'')'
       END
FROM sys.systypes;

-- tables des colonnes et transformation à effectuer pour la table à 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  c.is_computed = 0
  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 @@ROWCOUNT = 0
BEGIN
   RAISERROR('La table %s n''a pas d''autre colonne qu''une colonne IDENTITY. pas de résultat à produire !', 16, 1, @O_NAME) WITH NOWAIT;
   RETURN;
END;    

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

IF @ADD_BRACKET = 0
   SET @COLS = REPLACE(REPLACE(@COLS, '[', ''), ']', '');

IF @DEBUG = 1 
   RAISERROR('Liste des colonnes :%s.', 10, 1, @COLS) WITH NOWAIT;

--=============================================================================
-- 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' '
         + 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
   RAISERROR('Requête de génération : %s', 10, 1, @SQL) WITH NOWAIT;

-- 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

--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- PHASE 2 : application en procédure système                                --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--


--> fait de cette procédure une procédure marquée système 
EXEC sys.sp_MS_marksystemobject 'dbo.sp__GENERATE_INSERT';
GO

--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- EXEMPLE : liste toutes les tables de la base avec un max. de 1000 lignes  -- 
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--
/*
DECLARE @SAQL NVARCHAR(max) = N'';
SELECT @QL = @SQL + 'EXEC dbo.sp__GENERATE_INSERT ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', 0, 0, 1000, NULL, NULL, NULL, 0, 0, 0, 1;'
FROM   INFORMATION_SCHEMA.TABLES;
EXEC (@SQL)
*/