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