GÉNÉRER DES REQUETES INSERT À PARTIR DES DONNÉES EXISTANTES DANS UNE TABLE.

CREATE PROC sp_generate_inserts  
(  
 @table_name varchar(776),    -- La table / vue pour les commandes INSERT qui seront générées en utilisant les données existantes 
 @target_table varchar(776) = NULL,  -- Utilisez ce paramètre pour spécifier un autre nom de la table dans laquelle les données seront insérées  
 @include_column_list bit = 1,  -- Utilisez ce paramètre pour inclure / ne pas inclure colonne dans la liste générée INSERT  
 @from varchar(800) = NULL,   -- Ce paramètre permet de filtrer les lignes d'un filtre basé sur l'état (à l'aide WHERE)  
 @include_timestamp bit = 0,   -- Spécifier 1 pour ce paramètre si vous voulez inclure le TIMESTAMP / ROWVERSION colonne de données dans la déclaration INSERT  
 @debug_mode bit = 0,   -- Si @ debug_mode est 1, les requêtes SQL construits par cette procédure seront imprimés pour examen ultérieur 
 @owner varchar(64) = NULL,  -- Utilisez ce paramètre si vous n'êtes pas le propriétaire de la table  
 @ommit_images bit = 0,   -- Utilisez ce paramètre pour générer INSERT par l'omission des colonnes 'image'
 @ommit_identity bit = 0,  -- Utilisez ce paramètre pour ommettre les colonnes identity
 @top int = NULL,   -- Utilisez ce paramètre pour générer INSERT seulement pour les TOP n lignes   
 @cols_to_include varchar(8000) = NULL, -- Liste des colonnes à inclure dans la déclaration INSERT  
 @cols_to_exclude varchar(8000) = NULL, -- Liste des colonnes à être exclus de la commande INSERT  
 @disable_constraints bit = 0,  -- Lorsque 1, désactive clé étrangères et leur permet après l'INSERT  
 @ommit_computed_cols bit = 0  -- Lorsque 1, les colonnes calculées ne seront pas inclus dans la commande INSERT 
   
)  
AS  
BEGIN  
  
/***********************************************************************************************************                                       
Objectif: Pour générer des INSERT à partir des données existantes. Ces INSERTS peuvent être exécutés pour régénérer les données vers un autre endroit. Cette procédure peut également être utile afin de créer une base de données de configuration.
  
Exemple 1: Pour produire INSERT pour la table "titres": 
     
   EXEC sp_generate_inserts "titres"
  
Exemple 2: Pour ommettre la liste des colonnes dans la commande INSERT: (liste colonne est inclus par défaut) 
   IMPORTANT: Si vous avez trop de colonnes, je vous conseille d'ommettre  la liste de colonne, comme illustré ci-dessous, 
   Pour éviter les faux résultats 
     
   EXEC sp_generate_inserts "titres", @ include_column_list = 0
  
Exemple 3: Pour produire INSERT pour la table 'titlesCopy' vers la table 'titres' : 
   
   EXEC sp_generate_inserts 'titres', 'titlesCopy'
  
Exemple 4: Pour produire INSERT pour la table "titres" pour les seuls titres 
   Qui contiennent le mot «Computer» en eux: 
   NOTE: Ne pas compliquer le FROM ou clause WHERE ici. Il est supposé que vous êtes bien avec T-SQL si vous utilisez ce paramètre 
   
   EXEC sp_generate_inserts "titres", @ from = "from titres where titre like '%Computer%'"
  
Exemple 5: Pour spécifier que vous voulez inclure les données de la colonne TIMESTAMP dans la commande INSERT: 
   (Par défaut, les données de la colonne TIMESTAMP n'est pas scriptée) 
   
   EXEC sp_generate_inserts "titres", @ include_timestamp = 1
  
Exemple 6: Pour imprimer les informations de débogage: 
     
   EXEC sp_generate_inserts "titres", @ debug_mode = 1
  
Exemple 7: Si vous n'êtes pas le propriétaire de la table, utilisez le paramètre @propriétaire pour spécifier le nom du propriétaire 
   Pour utiliser cette option, vous devez avoir les permissions SELECT sur la table 
   
   EXEC sp_generate_inserts Nickstable, propriétaire @ = 'Lassaad'
  
Exemple 8: Pour produire INSERT pour le reste des colonnes exclusion images 
   Lorsque vous utilisez cette otion, ne pas mittre le paramètre @include_column_list à 0. 
   
   EXEC sp_generate_inserts imgtable, @ommit_images = 1
  
Exemple 9: Pour produire INSERT excluant (en omettant) les colonnes IDENTITY : 
   (Par défaut, les colonnes IDENTITY sont inclus dans la commande INSERT) 
   
   EXEC sp_generate_inserts mytable, @ommit_identity = 1
  
Exemple 10: Pour produire INSERT pour les 10 TOP lignes dans la table: 
     
   EXEC sp_generate_inserts mytable, @top = 10
  
Exemple 11: Pour produire des commandes INSERT avec les colonnes que vous voulez: 
     
   EXEC sp_generate_inserts titres, @cols_to_include = "'titre', 'titre_id', 'au_id'" 
  
Exemple 12: Pour produire des INSERT en omettant certaines colonnes: 
     
   EXEC sp_generate_inserts titres, cols_to_exclude @ = " 'title', 'title_id', 'au_id'"
  
Exemple 13: Afin d'éviter la vérification de la clé étrangères lors du chargement de données avec INSERT: 
     
   EXEC sp_generate_inserts titres, @disable_constraints = 1 
   
Exemple 14: Pour exclure les colonnes calculé
   EXEC sp_generate_inserts MaTable, @ommit_computed_cols = 1
***********************************************************************************************************/  
  
SET NOCOUNT ON  
  
--Faire en sorte que l'utilisateur utilise soit @cols_to_include soit @cols_to_exclude  
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))  
 BEGIN  
  RAISERROR('Utilisez soit @cols_to_include soit @cols_to_exclude. Ne pas utiliser ces deux paramètres à la fois',16,1)  
  RETURN -1 --Échec. Motif: Les deux paramètres : @cols_to_include et @cols_to_exclude sont spécifiés
 END  
  
--S'assurer que les paramètres  @cols_to_include et @cols_to_exclude reçoivent les valeurs dans le bon format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))  
 BEGIN  
  RAISERROR('Utilisation invalide de propriété @cols_to_include',16,1)  
  PRINT 'Spécifiez les noms de colonnes entouré de guillemets simples et séparés par des virgules'  
  PRINT 'Par exemple: EXEC sp_generate_inserts titres , @cols_to_include = "''titre_id'',''titres''"'  
  RETURN -1 --Échec. Cause: Utilisation invalide de propriété @cols_to_include 
 END  
  
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))  
 BEGIN  
  RAISERROR('Utilisation invalide de propriété @cols_to_exclude',16,1)  
  PRINT 'Spécifiez les noms de colonnes entouré de guillemets simples et séparés par des virgules'  
  PRINT 'Par exemple: EXEC sp_generate_inserts titres, @cols_to_exclude = "''titre_id'',''titre''"'  
  RETURN -1 --Échec. Cause: Utilisation invalide de propriété @cols_to_exclude
 END  
  
  
-- Contrôl pour voir si le nom de base de données est spécifiée avec le même nom de table 
-- Le contexte de votre base de données local devraient être la table pour laquelle vous voulez générer INSERT 
-- Précise si le nom de base de données n'est pas autorisé
IF (PARSENAME(@table_name,3)) IS NOT NULL  
 BEGIN  
  RAISERROR('Ne pas spécifier le nom de la base. Être dans la base de données nécessaire et spécifier juste le nom de la table.',16,1)  
  RETURN -1 --Échec. Motif: Le nom de la base de donnée est spécifiée avec le nom de la table, ce qui n'est pas autorisé
 END  
  
-- Vérification de l'existence de table 'user'ou 'vision ' 
-- Cette procédure n'est pas écrite pour travailler sur les tables système 
-- Pour l'écriture des données dans les tables système, il suffit de créer une vue sur les tables système et de travailler sur cette vue
  
IF @owner IS NULL  
 BEGIN  
  IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))   
   BEGIN  
    RAISERROR('Table ou vue n''a pas été trouvée.',16,1)  
    PRINT 'Vous pouvez voir cette erreur, si vous n''êtes pas le propriétaire de cette table ou vue. Dans ce cas, utilisez le paramètre @owner pour spécifier le nom du propriétaire.'  
    PRINT 'Assurez-vous d''avoir la permission de SELECT sur la table ou vue.'  
    RETURN -1 --Échec. Raison: il n'existe pas de table ou vue utilisateur avec ce nom 
   END  
 END  
ELSE  
 BEGIN  
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)  
   BEGIN  
    RAISERROR('Table ou vue n''a pas été trouvée.',16,1)  
    PRINT 'Vous pouvez voir cette erreur, si vous n''êtes pas le propriétaire de cette table ou vue. Dans ce cas, utilisez le paramètre @owner pour spécifier le nom du propriétaire.'  
    PRINT 'Assurez-vous d''avoir la permission de SELECT sur la table ou vue.'  
    RETURN -1 --Échec. Raison: il n'existe pas de table ou vue utilisateur avec ce nom   
   END  
 END  
  
-- Déclaration des Variables
DECLARE  @Column_ID int,     
  @Column_List varchar(8000),   
  @Column_Name varchar(8000),   
  @Start_Insert varchar(786),   
  @Data_Type varchar(128),   
  @Actual_Values varchar(8000), --Telle est la chaîne qui sera finalement exécuté pour générer des commandes INSERT
  @IDN varchar(128)  --Contiendra le nom de la colonnes IDENTITY dans la table  
  
-- Initialisation des Variables
SET @IDN = ''  
SET @Column_ID = 0  
SET @Column_Name = ''  
SET @Column_List = ''  
SET @Actual_Values = ''  
  
IF @owner IS NULL   
 BEGIN  
  SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'   
 END  
ELSE  
 BEGIN  
  SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'     
 END  
  
  
--Pour obtenir l'ID de la premiere colonne
  
SELECT @Column_ID = MIN(ORDINAL_POSITION)    
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)   
WHERE  TABLE_NAME = @table_name AND  
(@owner IS NULL OR TABLE_SCHEMA = @owner)  
  
  
  
--Boucle à travers toutes les colonnes de la table, pour obtenir les noms des colonnes et leurs types de données
WHILE @Column_ID IS NOT NULL  
 BEGIN  
  SELECT  @Column_Name = QUOTENAME(COLUMN_NAME),   
  @Data_Type = DATA_TYPE   
  FROM  INFORMATION_SCHEMA.COLUMNS (NOLOCK)   
  WHERE  ORDINAL_POSITION = @Column_ID AND   
  TABLE_NAME = @table_name AND  
  (@owner IS NULL OR TABLE_SCHEMA = @owner)  
  
  
  
  IF @cols_to_include IS NOT NULL --Sélectionne uniquement les colonnes spécifiées par l'utilisateur  
  BEGIN  
   IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0   
   BEGIN  
    GOTO SKIP_LOOP  
   END  
  END  
  
  IF @cols_to_exclude IS NOT NULL --Sélectionne uniquement les colonnes spécifiées par l'utilisateur
  BEGIN  
   IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0   
   BEGIN  
    GOTO SKIP_LOOP  
   END  
  END  
  
  --S'assurer de la sortie SET IDENTITY_INSERT ON / OFF dans le cas où la table a une colonne de type IDENTITY
  IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1   
  BEGIN  
   IF @ommit_identity = 0 --Déterminer s'il ya lieu d'inclure ou d'exclure la colonne IDENTITY
    SET @IDN = @Column_Name  
   ELSE  
    GOTO SKIP_LOOP     
  END  
    
  --Faire en sorte que de'afficher les colonnes calculées ou non  
  IF @ommit_computed_cols = 1  
  BEGIN  
   IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1   
   BEGIN  
    GOTO SKIP_LOOP       
   END  
  END  
    
  --Les table avec les colonnes de type de données IMAGE ne sont pas pris en charge pour des raisons évidentes
  IF(@Data_Type in ('image'))  
   BEGIN  
    IF (@ommit_images = 0)  
     BEGIN  
      RAISERROR('Table avec des colonnes image ne sont pas supportés.',16,1)  
      PRINT 'Utilisez le paramètre @ommit_images  = 1 pour générer un INSERTs pour le reste des colonnes.'  
      PRINT 'NE PAS ommettre la Liste des colonne dans l''INSERT. En utilisant @include_column_list = 0 vous oublier la liste des colonne , les INSERTs généré vont échouer.'  
      RETURN -1 --Échec. Raison: Il y a une colonne avec le type de données d'IMAGE
     END  
    ELSE  
     BEGIN  
     GOTO SKIP_LOOP  
     END  
   END  
  
-- Déterminer le type de données de la colonne et en fonction du type de données, la part des VALEURS 
-- La commande INSERT est générée. Des mesures sont prises pour traiter les colonnes avec des valeurs NULL. Également 
-- Faire en sorte, pour ne pas perdre toutes les données de type flot, real, money, smallmomey, datetime  
  SET @Actual_Values = @Actual_Values  +  
  CASE   
   WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')   
    THEN   
     'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'  
   WHEN @Data_Type IN ('datetime','smalldatetime')   
    THEN   
     'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'  
   WHEN @Data_Type IN ('uniqueidentifier')   
    THEN    
     'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'  
   WHEN @Data_Type IN ('text','ntext')   
    THEN    
     'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'       
   WHEN @Data_Type IN ('binary','varbinary')   
    THEN    
     'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'    
   WHEN @Data_Type IN ('timestamp','rowversion')   
    THEN    
     CASE   
      WHEN @include_timestamp = 0   
       THEN   
        '''DEFAULT'''   
       ELSE   
        'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'    
     END  
   WHEN @Data_Type IN ('float','real','money','smallmoney')  
    THEN  
     'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'   
   ELSE   
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'   
  END   + '+' +  ''',''' + ' + '  
    
  --Génération de la liste des colonnes de la commande INSERT
  SET @Column_List = @Column_List +  @Column_Name + ','   
  
  SKIP_LOOP: --L'étiquette utilisée en GOTO
  
  SELECT  @Column_ID = MIN(ORDINAL_POSITION)   
  FROM  INFORMATION_SCHEMA.COLUMNS (NOLOCK)   
  WHERE  TABLE_NAME = @table_name AND   
  ORDINAL_POSITION > @Column_ID AND  
  (@owner IS NULL OR TABLE_SCHEMA = @owner)  
  
  
 --Boucle se termine ici! 
 END  
  
--Pour se débarrasser des caractères supplémentaires qui se sont concaténées au cours de la dernière exécution dans la boucle
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)  
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)  
  
IF LTRIM(@Column_List) = ''   
 BEGIN  
  RAISERROR('Aucune colonne à sélectionner. Il devrait au moins être une colonne pour générer la sortie',16,1)  
  RETURN -1 --Échec. Motif: On dirait que toutes les colonnes sont omise en utilisant le paramètre @cols_to_exclude
 END  
  
--Génération de la dernière chaîne qui sera exécuté, à la sortie de l'INSERT
IF (@include_column_list <> 0)  
 BEGIN  
  SET @Actual_Values =   
   'SELECT ' +    
   CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +   
   '''' + RTRIM(@Start_Insert) +   
   ' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' +   
   ' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' +   
   COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')  
 END  
ELSE IF (@include_column_list = 0)  
 BEGIN  
  SET @Actual_Values =   
   'SELECT ' +   
   CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +   
   '''' + RTRIM(@Start_Insert) +   
   ' '' +''VALUES(''+ ' +  @Actual_Values + '+'')''' + ' ' +   
   COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')  
 END   
  
--À déterminer si on affche les informations de déboguage
IF @debug_mode =1  
 BEGIN  
  PRINT '/*****DÉBUT DES INFORMATIONS DE DÉBOGAGE*****'  
  PRINT 'Début de la commande INSERT:'  
  PRINT @Start_Insert  
  PRINT ''  
  PRINT 'La liste des colonnes:'  
  PRINT @Column_List  
  PRINT ''  
  PRINT 'La commande SELECT exécuté pour générer les INSERTs'  
  PRINT @Actual_Values  
  PRINT ''  
  PRINT '*****FIN DES INFORMATIONS DE DÉBOGAGE*****/'  
  PRINT ''  
 END  
      
--Déterminer s'il ya lieu d'imprimer IDENTITY_INSERT ou non
IF (@IDN <> '')  
 BEGIN  
  PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'  
  PRINT 'GO'  
  PRINT ''  
 END  
  
  
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)  
 BEGIN  
  IF @owner IS NULL  
   BEGIN  
    SELECT  'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code pour désactiver temporairement les contraintes'  
   END  
  ELSE  
   BEGIN  
    SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code de désactiver temporairement les contraintes'  
   END  
  
  PRINT 'GO'  
 END  
  
PRINT ''  
PRINT 'PRINT ''INSERTION DES DONNÉE DANS ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''  
  
  
--Tout le travail acharné porte ses fruits ici! Vous obtiendrez votre INSERT, quand la ligne suivante exécute! 
EXEC (@Actual_Values)  
  
PRINT 'PRINT ''OK'''  
PRINT ''  
  
  
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)  
 BEGIN  
  IF @owner IS NULL  
   BEGIN  
    SELECT  'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL'  AS '--Code pour activer les contraintes précédemment désactivé'  
   END  
  ELSE  
   BEGIN  
    SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code pour activer les contraintes précédemment désactivé'  
   END  
  
  PRINT 'GO'  
 END  
  
PRINT ''  
IF (@IDN <> '')  
 BEGIN  
  PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'  
  PRINT 'GO'  
 END  
  
PRINT 'SET NOCOUNT OFF'  
  
  
SET NOCOUNT OFF  
RETURN 0 -- C'est enfin fini :)
END

Télécharger le zip

13 février 2008 18:06:04 :

modification de la description

28 février 2008 14:38:15 :

Ajout du script en download

• SQL SERVER - PROCEDURE D'EDITION D'UN FICHIER PLAT (EXPORT CSV)
• PROCÉDURE INSERTION OU MISE À JOUR DONNÉES D'UN UTILISATEUR
• REQUETE SUPPRESSION DE DOUBLON
• CHANGER TOUTES LES COLONNES D'UNE BASES DE DONNÉES D'UN TYPE VERS UN AUTRE [SQL 2000]
• PACKAGE ORACLE -- OUTILS APPLICATIFS

• PROCÉDURE INSERTION OU MISE À JOUR DONNÉES D'UN UTILISATEUR
• INSERTION DE DONNÉES
• SQL SERVER - UNE PROC STOCK FAISANT SOIT UN UPDATE SOIT UN INSERT