Rétro ingéniérie des données SQL Server : exporter les lignes sous forme INSERT

Cet article propose une procédure afin d’exporter les données de vos tables sous forme d’ordre INSERT INTO.
Nous poursuivrons cette série d’article consacré à la rétro ingénierie avec un article consacré à l’écriture d’une procédure de création des tables, puis une dernière consacrée aux objets de code (fonctions, procédures, triggers).


CREATE PROCEDURE P_RETRO_INSERT @TABLE NVARCHAR(128),  
                                @SCHEMA NVARCHAR(128) = 'dbo',  
                                @IDENT_FORCE BIT = 1  
AS  
/******************************************************************************  
* Frédéric Brouard - SQLpro - http://www.sqlspot.com - 2008-09-02             *  
*******************************************************************************  
* Exporter les données des tables sous forme d'ordre INSERT INTO              *  
*******************************************************************************
* paramètres : @SCHEMA       nom du schéma de la table (si NULL ou vide dbo)  *  
*              @TABLE        nom de la table                                  *  
*              @IDENT_FORCE  force l'insertion des autoincréments             *  
*******************************************************************************  
* NOTA : - Les types image, sql_variant, xml, rowversion, timestamp, binary,  *  
*          varbinary et xml ne sont pas supportés dans cette version          *  
*        - valable pour SQL server 2005 et suivants                           *  
*******************************************************************************  
* copyright F. Brouard / SQLpro / SQLspot                                     *  
*******************************************************************************  
* MODIFICATIONS :                                                             *  
*    - rajout de "AS SQL_COMMAND" dans la ligne :                             *
*      SET @SQL = 'SELECT ''' + @SQLFIX + ''' + SQL_COMMAND +'') '' ...       *
*    - suppression des tirets dans les dates                                  *
*    - ajout des types date, time et datetime2                                *
*    - supression des blancs inutile des valeurs chaînes de caractères        *
*    - rajout de la gestion des NULL dans les valeurs retournées              *
******************************************************************************/  
 
-- met le schéma à dbo à défaut  
SET @SCHEMA = COALESCE(NULLIF(@SCHEMA , ''), 'dbo');  
 
-- regarde si la table existe bien  
IF NOT EXISTS(SELECT *  
              FROM   INFORMATION_SCHEMA.TABLES  
              WHERE  TABLE_SCHEMA = @SCHEMA  
                AND  TABLE_NAME = @TABLE  
                AND  TABLE_TYPE = 'BASE TABLE')  
   BEGIN  
      SELECT @SCHEMA = COALESCE(@SCHEMA, ''), @TABLE = COALESCE(@TABLE, '')  
      RAISERROR('Aucune table de nom %s.%s n''a été trouvée dans la base courante.', 16, 1, @SCHEMA, @TABLE);  
      RETURN;  
   END  
 
-- préparation des variables  
DECLARE @OBJ NVARCHAR(261);  
SET @OBJ = '[' +@SCHEMA+'].[' + @TABLE+']'  
DECLARE @SQLFIX NVARCHAR(max), @SQLVAL NVARCHAR(max), @SQLCOL NVARCHAR(max), @SQL NVARCHAR(max);  
SELECT @SQLFIX = 'INSERT INTO ' + @OBJ + ' (';  
 
-- construit la partie fixe de la chaîne de requête dynamique  
SELECT @SQLFIX = @SQLFIX + '['+ COLUMN_NAME +'], '  
FROM   INFORMATION_SCHEMA.COLUMNS  
WHERE  TABLE_SCHEMA = @SCHEMA  
  AND  TABLE_NAME  = @TABLE  
ORDER  BY ORDINAL_POSITION;  
 
SELECT @SQLFIX = SUBSTRING(@SQLFIX, 1, LEN(@SQLFIX) -1) +') VALUES (', @SQLVAL = 'SELECT ', @SQLCOL = '';  
 
-- construit la partie variable de la chaîne de requête  
SELECT @SQLVAL = @SQLVAL +  
       CASE  
          WHEN DATA_TYPE IN ('nchar', 'nvarchar', 'char', 'varchar', 'text', 'ntext')  
             THEN 'COALESCE('''''''' + REPLACE(RTRIM('+COLUMN_NAME+'), '''''''', '''''''''''') +'''''''', ''NULL'') +'', ''+ '              
          WHEN DATA_TYPE IN ('float', 'real', 'decimal', 'smallint', 'int', 'bigint', 'tinyint', 'bit',  
                             'money', 'smallmoney')  
             THEN 'COALESCE(CAST('+COLUMN_NAME+' AS VARCHAR(128)), ''NULL'') +'', ''+ '              
          WHEN DATA_TYPE IN ('datetime', 'smalldatetime', 'date', 'time', 'datetime2')  
             THEN 'COALESCE('''''''' + REPLACE(CONVERT(VARCHAR(32), '+COLUMN_NAME+', 121), ''-'', '''')+ '''''''', ''NULL'') +'', ''+ '              
          WHEN DATA_TYPE IN ('uniqueidentifier')  
             THEN 'COALESCE('''''''' + CAST('+COLUMN_NAME+'AS VARCHAR(64))+'''''''', ''NULL'') +'', ''+ '  
          ELSE ''  
       END,  
       @SQLCOL = @SQLCOL +  
       CASE  
          WHEN DATA_TYPE IN ('nchar', 'nvarchar', 'char', 'varchar', 'text', 'ntext',  
                             'float', 'real', 'decimal', 'smallint', 'int', 'bigint', 'tinyint', 'bit',  
                             'money', 'smallmoney', 'datetime', 'smalldatetime',  
                             'date', 'time', 'datetime2', 'uniqueidentifier')  
             THEN COLUMN_NAME +', '  
          ELSE ''  
       END  
FROM   INFORMATION_SCHEMA.COLUMNS  
WHERE  TABLE_SCHEMA = @SCHEMA  
  AND  TABLE_NAME  = @TABLE  
ORDER  BY ORDINAL_POSITION;  
 
SET @SQLCOL = SUBSTRING(@SQLCOL, 1,  LEN(@SQLCOL) - 2);  
 
-- réencapsule la requête afin d'avoir les données dans l'ordre de la première colonne  
SELECT @SQLVAL = SUBSTRING(@SQLVAL, 1, LEN(@SQLVAL) -6) +' AS SQL_COMMAND, ROW_NUMBER() OVER(ORDER BY '  
       + MIN(COLUMN_NAME)+') AS N FROM ' + @OBJ  
FROM   INFORMATION_SCHEMA.COLUMNS  
WHERE  TABLE_SCHEMA = @SCHEMA  
  AND  TABLE_NAME  = @TABLE  
  AND  ORDINAL_POSITION = 1;  
 
-- réencapsule la requête afin de générer les données de la table  
SET @SQLVAL = 'SELECT SQL_COMMAND, N FROM (' + @SQLVAL +') AS T ';  
SET @SQL = 'SELECT ''' + @SQLFIX + ''' + SQL_COMMAND +'') '' as SQL_COMMAND, N  FROM (' + @SQLVAL +') AS TT';  
 
-- en cas de présence d'identity et de demande de forçage...  
IF @IDENT_FORCE = 1 AND NOT EXISTS(SELECT *  
                                   FROM   INFORMATION_SCHEMA.COLUMNS  
                                   WHERE  COLUMNPROPERTY(OBJECT_ID(@OBJ), COLUMN_NAME, 'IsIdentity') = 1  
                                     AND  TABLE_SCHEMA = @SCHEMA  
                                     AND  TABLE_NAME  = @TABLE)  
   SET @IDENT_FORCE = 0  
 
-- ...ajoute les commande de débranchement et de rebranchement  
IF @IDENT_FORCE = 1  
SET @SQL = 'SELECT CAST(''SET IDENTITY INSERT ' + @OBJ + ' ON'' AS VARCHAR(max)) AS SQL_COMMAND, CAST(0 AS BIGINT) AS N UNION ALL ' + @SQL +  
           ' UNION ALL SELECT CAST(''SET IDENTITY INSERT ' + @OBJ + ' OFF'' AS VARCHAR(max)) AS SQL_COMMAND, CAST(9223372036854775807 AS BIGINT) AS N'  
 
-- réencapsule le tout en requête  
SET @SQL = 'SELECT SQL_COMMAND + '';''  FROM (' + @SQL + ') AS TTT ORDER BY N;'  
 
-- exécute la requête finale  
EXEC (@SQL)  
GO

Exemple d’utilisation :
EXEC P_RETRO_INSERT 'T_CLIENT', 'dbo', 0

***
Frédéric BROUARD – SQLpro – MVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation

3 réflexions au sujet de « Rétro ingéniérie des données SQL Server : exporter les lignes sous forme INSERT »

  1. Avatar de sqlprosqlpro Auteur de l’article

    Améliorations apportées :
    – suppression des tirets dans les dates
    – ajout des types date, time et datetime2
    – suppression des blancs inutile des valeurs chaînes de caractères
    – rajout de la gestion des NULL dans les valeurs retournées

  2. Avatar de PtoléméePtolémée

    Merci Frédéric pour cet excellent script. J’ai longtemps cherché dans SQL 2005 une fonction automatique, un « générer script » ou « exporter données » pour le faire. En vain. Étonnant de la part de SQL Server !

    Petites remarques :

    1) S’il y a des attributs à NULL cela ne marche pas.

    2) Il manque un « AS SQL_COMMAND » dans la requête TT :
    SET @SQL = ‘SELECT  »’ + @SQLFIX +  »’ + SQL_COMMAND + »)  » as SQL_COMMAND, N FROM (‘ + @SQLVAL +’) AS TT';

    Après ça marche.

    Merci.

    Eric DD

Laisser un commentaire