Question Quel est le meilleur moyen de générer automatiquement des instructions INSERT pour une table SQL Server?


Nous écrivons une nouvelle application et, tout en testant, nous aurons besoin d'un tas de données factices. J'ai ajouté ces données en utilisant MS Access pour vider les fichiers Excel dans les tables pertinentes.

De temps en temps, nous voulons "rafraîchir" les tables pertinentes, ce qui signifie les supprimer toutes, les recréer et exécuter une requête d'ajout MS Access sauvegardée.

La première partie (déposer et recréer) est un simple script SQL, mais la dernière partie me fait reculer. Je veux un script de configuration unique qui a un tas d'INSERT pour régénérer les données factices.

J'ai les données dans les tableaux maintenant. Quel est le meilleur moyen de générer automatiquement une grande liste d'instructions INSERT à partir de cet ensemble de données?

La seule façon de le faire est de sauvegarder la table sur une feuille Excel puis d'écrire une formule Excel pour créer un INSERT pour chaque ligne, ce qui n'est sûrement pas la meilleure solution.

J'utilise le Management Studio 2008 pour me connecter à une base de données SQL Server 2005.


482
2018-06-11 17:42


origine


Réponses:


Microsoft devrait annoncer cette fonctionnalité de SSMS 2008. La fonctionnalité que vous recherchez est intégrée dans Générer un script utilitaire, mais la fonctionnalité est désactivée par défaut et doit être activée lors de l'écriture d'un script dans une table.

Ceci est un passage rapide pour générer le INSERT des instructions pour toutes les données de votre table, sans utiliser de scripts ni de compléments pour SQL Management Studio 2008:

  1. Faites un clic droit sur la base de données et allez à les tâches > Générer des scripts.
  2. Sélectionnez les tables (ou objets) sur lesquelles vous souhaitez générer le script.
  3. Aller à Définir les options de script onglet et cliquez sur le Avancée bouton.
  4. dans le Général catégorie, aller à Type de données à écrire
  5. Il y a 3 options: Schéma seulement, Données uniquement, et Schéma et données. Sélectionnez l'option appropriée et cliquez sur D'accord.

Vous obtiendrez alors le CREATE TABLE déclaration et tous les INSERT déclarations pour les données directement à partir de SSMS.


837
2017-08-22 16:11



Nous utilisons cette procédure stockée - elle vous permet de cibler des tables spécifiques et d'utiliser des clauses where. Vous pouvez trouver le texte ici.

Par exemple, cela vous permet de faire ceci:

EXEC sp_generate_inserts 'titles'

76
2018-06-11 17:46



Comme mentionné par @Mike Ritacco mais mis à jour pour SSMS 2008 R2

  1. Faites un clic droit sur le nom de la base de données
  2. Choisissez Tâches> Générer des scripts
  3. En fonction de vos paramètres, la page d'introduction peut s'afficher ou non
  4. Choisissez 'Sélectionner des objets de base de données spécifiques',
  5. Développez l'arborescence et vérifiez les tables pertinentes
  6. Cliquez sur Suivant
  7. Cliquez sur Avancé
  8. Sous la section Général, choisissez l'option appropriée pour 'Types de données à script'
  9. Terminez l'assistant

Vous obtiendrez alors toutes les instructions INSERT pour les données directement à partir de SSMS.

EDIT 2016-10-25 SQL Server 2016 / SSMS 13.0.15900.1

  1. Faites un clic droit sur le nom de la base de données

  2. Choisissez Tâches> Générer des scripts

  3. En fonction de vos paramètres, la page d'introduction peut s'afficher ou non

  4. Choisissez 'Sélectionner des objets de base de données spécifiques',

  5. Développez l'arborescence et vérifiez les tables pertinentes

  6. Cliquez sur Suivant

  7. Cliquez sur Avancé

  8. Dans la section Général, choisissez l'option appropriée pour 'Types de données à scénario'

  9. Cliquez sur OK

  10. Indiquez si vous souhaitez que la sortie accède à une nouvelle requête, au presse-papiers ou à une autre requête. fichier

  11. Cliquez deux fois sur Suivant

  12. Votre script est préparé conformément aux paramètres que vous avez choisis ci-dessus

  13. Cliquez sur Terminer


40
2017-12-02 13:53



Vous pouvez utiliser SSMS Tools Pack (disponible pour SQL Server 2005 et 2008). Il est livré avec une fonctionnalité pour générer des instructions d'insertion.

http://www.ssmstoolspack.com/


27
2017-07-12 19:48



Cela peut être fait en utilisant Visual Studio aussi (au moins dans la version 2013).

Dans VS 2013, il est également possible à filtrer la liste des lignes sur lesquelles l’insertion est basée est quelque chose d’impossible pour SSMS comme je le sais.

Effectuez les étapes suivantes:

  • Ouvrez la fenêtre "SQL Server Object Explorer" (menu: / View / SQL Server Object Explorer)
  • Ouvrir / développer la base de données et ses tables
  • Cliquez avec le bouton droit sur la table et choisissez "Afficher les données" dans le menu contextuel.
  • Cela affichera les données dans la zone principale
  • Étape facultative: Cliquez sur l'icône de filtre "Trier et filtrer l'ensemble de données" (la quatrième icône de gauche sur la ligne au-dessus du résultat) et appliquez un filtre à une ou plusieurs colonnes.
  • Cliquez sur les icônes "Script" ou "Script to File" (les icônes à droite de la rangée supérieure ressemblent à de petites feuilles de papier)

Cela créera les instructions d'insertion (conditionnelles) pour la table sélectionnée dans la fenêtre ou le fichier actif.


Les boutons "Filtrer" et "Script" Visual Studio 2013:

enter image description here


24
2018-04-05 12:41



J'utilise SSMS 2008 version 10.0.5500.0. Dans cette version faisant partie de l'assistant Générer des scripts, au lieu d'un bouton Avancé, se trouve l'écran ci-dessous. Dans ce cas, je voulais juste les données insérées et pas de créer des déclarations, donc je devais changer les deux propriétés encercléesScript Options


23
2018-04-05 08:01



La procédure stockée de Jane Dallaway: http://docs.google.com/leaf?id=0B_AkC4ZdTI9tNWVmZWU3NzAtMWY1My00NjgwLWI3ZjQtMTY1NDMxYzBhYzgx&hl=fr. La documentation est une série d'articles de blog: https://www.google.com/search?q=spu_generateinsert&as_sitesearch=http%3A%2F%2Fjane.dallaway.com


13
2017-07-20 17:19



Le premier lien vers sp_generate_inserts est plutôt cool, voici une version très simple:

DECLARE @Fields VARCHAR(max); SET @Fields = '[QueueName], [iSort]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Queues'               -- your table

DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) + 
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''') +' FROM ' + @Table + '
PRINT @S'

EXEC (@SQL)

Sur mon système, j'obtiens ce résultat:

INSERT INTO Queues([QueueName], [iSort])
SELECT 'WD: Auto Capture', '10' UNION 
SELECT 'Car/Lar', '11' UNION 
SELECT 'Scan Line', '21' UNION 
SELECT 'OCR', '22' UNION 
SELECT 'Dynamic Template', '23' UNION 
SELECT 'Fix MICR', '41' UNION 
SELECT 'Fix MICR (Supervisor)', '42' UNION 
SELECT 'Foreign MICR', '43' UNION 
...

7
2018-06-11 19:08



Si vous avez besoin d'un accès par programme, vous pouvez utiliser une procédure stockée open source `GenerateInsert.

INSÉRER générateur (s) d'instruction

Juste comme un exemple simple et rapide, pour générer des instructions INSERT pour une table AdventureWorks.Person.AddressType exécuter les instructions suivantes:

USE [AdventureWorks];
GO
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';

Cela va générer le script suivant:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
VALUES
 (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

6
2017-12-06 03:54



Ma contribution au problème, un générateur de script Powershell INSERT qui vous permet de scripter plusieurs tables sans avoir à utiliser l'interface graphique SSMS encombrante. Idéal pour la persistance rapide des données "seed" dans le contrôle de la source.

  1. Enregistrez le script ci-dessous en tant que "filename.ps1".
  2. Apportez vos propres modifications aux zones sous "PERSONNALISER MOI".
  3. Vous pouvez ajouter la liste des tables au script dans n'importe quel ordre.
  4. Vous pouvez ouvrir le script dans Powershell ISE et cliquer sur le bouton Lecture, ou simplement exécuter le script dans l'invite de commande Powershell.

Par défaut, le script INSERT généré sera "SeedData.sql" sous le même dossier que le script.

Vous aurez besoin des assemblys d'objets de gestion SQL Server installés, qui devraient être là si vous avez installé le SSMS.

Add-Type -AssemblyName ("Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
Add-Type -AssemblyName ("Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")



#CUSTOMIZE ME
$outputFile = ".\SeedData.sql"
$connectionString = "Data Source=.;Initial Catalog=mydb;Integrated Security=True;"



$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conn)
$db = $srv.Databases[$srv.ConnectionContext.DatabaseName]
$scr = New-Object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scr.Options.FileName = $outputFile
$scr.Options.AppendToFile = $false
$scr.Options.ScriptSchema = $false
$scr.Options.ScriptData = $true
$scr.Options.NoCommandTerminator = $true

$tables = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection



#CUSTOMIZE ME
$tables.Add($db.Tables["Category"].Urn)
$tables.Add($db.Tables["Product"].Urn)
$tables.Add($db.Tables["Vendor"].Urn)



[void]$scr.EnumScript($tables)

$sqlConnection.Close()

5
2018-04-21 23:42