Générer des requetes insert à partir des données existantes dans une table.

Soyez le premier à donner votre avis sur cette source.

Vue 26 627 fois - Téléchargée 646 fois

Description

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.

Source / Exemple :


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

Conclusion :


NOTE: Cette procédure peut ne pas fonctionner avec les tables qui ont beaucoup de colonnes. Les résultats peuvent être imprévisibles avec d'énormes colonnes de texte. Dans la mesure du possible, pour de meilleurs résultats, utilisez le paramètre @include_column_list afin d'ommèttre une liste de colonne dans INSERT.

Codes Sources

A voir également

Ajouter un commentaire

Commentaires

Commenter la réponse de bsargos

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.