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