visual Was ist der beste Weg, INSERT-Anweisungen für eine SQL Server-Tabelle automatisch zu generieren?




vs code sql (16)

Wir schreiben eine neue Anwendung, und während des Testens werden wir eine Menge Dummy-Daten benötigen. Ich habe diese Daten hinzugefügt, indem ich MS Access verwendet habe, um Excel-Dateien in die relevanten Tabellen zu dumpen.

Hin und wieder möchten wir die relevanten Tabellen "aktualisieren", was bedeutet, dass sie alle gelöscht, neu erstellt und eine gespeicherte MS Access-Anfrage ausgeführt werden.

Der erste Teil (drop & re-creating) ist ein einfaches sql-Skript, aber der letzte Teil lässt mich zusammenzucken. Ich möchte ein einzelnes Setup-Skript, das eine Reihe von INSERTs hat, um die Dummy-Daten neu zu generieren.

Ich habe jetzt die Daten in den Tabellen. Was ist der beste Weg, um automatisch eine große Liste von INSERT-Anweisungen aus diesem Datensatz zu generieren?

Ich denke an etwas wie in TOAD (für Oracle), wo Sie mit der rechten Maustaste auf ein Gitter klicken und auf Speichern unter-> Anweisungen einfügen klicken können, und es wird einfach ein großes SQL-Skript ablegen, wo immer Sie wollen.

Der einzige Weg, den ich mir vorstellen kann, ist, die Tabelle in ein Excel-Blatt zu speichern und dann eine Excel-Formel zu schreiben, um für jede Zeile ein INSERT zu erstellen, was sicherlich nicht der beste Weg ist.

Ich verwende das Management Studio 2008, um eine Verbindung mit einer SQL Server 2005-Datenbank herzustellen.


Ich verwende SSMS 2008 Version 10.0.5500.0. In dieser Version als Teil des Generate Scripts-Assistenten wird anstelle einer Schaltfläche "Advanced" (Erweitert) der folgende Bildschirm angezeigt. In diesem Fall wollte ich nur die Daten einfügen und keine create-Anweisungen, also musste ich die beiden eingekreisten Eigenschaften ändern


Verwenden Sie keine Einsätze, verwenden Sie BCP


Haben Sie bereits Daten in einer Produktionsdatenbank? Wenn dies der Fall ist, können Sie eine Periodenaktualisierung der Daten über DTS einrichten. Wir machen wöchentlich an den Wochenenden und es ist sehr schön, jede Woche saubere, echte Daten für unsere Tests zu haben.

Wenn Sie noch keine Produktion haben, sollten Sie eine Datenbank erstellen, die sie wollen (frisch). Dann duplizieren Sie diese Datenbank und verwenden Sie diese neu erstellte Datenbank als Ihre Testumgebung. Wenn du die saubere Version willst, duplizierst du einfach deine saubere und Bobs Onkel .


Wie von @Mike Ritacco erwähnt, aber für SSMS 2008 R2 aktualisiert

  1. Klicken Sie mit der rechten Maustaste auf den Namen der Datenbank
  2. Wählen Sie Aufgaben> Skripts generieren
  3. Abhängig von Ihren Einstellungen kann die Intro-Seite angezeigt werden oder nicht
  4. Wählen Sie 'Bestimmte Datenbankobjekte auswählen',
  5. Erweitern Sie die Baumansicht und prüfen Sie die relevanten Tabellen
  6. Weiter klicken
  7. Klicken Sie auf Erweitert
  8. Wählen Sie unter Allgemein die entsprechende Option für 'Datentypen zum Skript' aus.
  9. Schließen Sie den Assistenten ab

Sie erhalten dann alle INSERT-Anweisungen für die Daten direkt aus SSMS.

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

  1. Klicken Sie mit der rechten Maustaste auf den Namen der Datenbank

  2. Wählen Sie Aufgaben> Skripts generieren

  3. Abhängig von Ihren Einstellungen kann die Intro-Seite angezeigt werden oder nicht

  4. Wählen Sie 'Bestimmte Datenbankobjekte auswählen',

  5. Erweitern Sie die Baumansicht und prüfen Sie die relevanten Tabellen

  6. Weiter klicken

  7. Klicken Sie auf Erweitert

  8. Wählen Sie unter Allgemein die entsprechende Option für 'Datentypen zum Skript' aus.

  9. OK klicken

  10. Wählen Sie aus, ob die Ausgabe an eine neue Abfrage, die Zwischenablage oder eine Datei gesendet werden soll

  11. Klicken Sie zweimal auf Weiter

  12. Ihr Skript wird gemäß den Einstellungen vorbereitet, die Sie oben ausgewählt haben

  13. Klicken Sie auf Fertig stellen


Wenn Sie einen programmatischen Zugriff benötigen, können Sie eine Open Source-Stored Procedure `GenerateInsert verwenden.

INSERT-Anweisung (en) Generator

Als ein einfaches und schnelles Beispiel, um INSERT-Anweisungen für eine Tabelle AdventureWorks.Person.AddressType zu generieren, führen Sie folgende Anweisungen aus:

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

Dies erzeugt das folgende Skript:

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

Wir verwenden diese gespeicherte Prozedur - es ermöglicht Ihnen, auf bestimmte Tabellen zu zielen und where-Klauseln zu verwenden. Sie können den Text here .

Zum Beispiel können Sie dies tun: Um INSERT-Anweisungen für Tabellentitel zu erzeugen:

EXEC sp_generate_inserts 'titles'

Der erste Link zu sp_generate_inserts ist ziemlich cool, hier ist eine wirklich einfache Version:

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)

Auf meinem System bekomme ich dieses Ergebnis:

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

Nicht sicher, ob ich deine Frage richtig verstanden habe.

Wenn Sie Daten in MS-Access haben, die Sie zu SQL Server verschieben möchten, können Sie DTS verwenden.
Und ich nehme an, Sie könnten SQL Profiler verwenden, um alle INSERT-Anweisungen zu sehen, die wahrscheinlich gehen.



Sie können das SSMS Tools Pack verwenden (verfügbar für SQL Server 2005 und 2008). Es enthält eine Funktion zum Generieren von Einfügeanweisungen.

http://www.ssmstoolspack.com/


Dies kann auch mit Visual Studio geschehen (mindestens ab Version 2013).

In VS 2013 ist es auch möglich , die Liste der Zeilen, auf denen die INSTS-Anweisung basiert, zu filtern , was in SSMS nicht möglich ist, soweit ich weiß.

Führen Sie die folgenden Schritte aus:

  • Öffnen Sie das Fenster "SQL Server Object Explorer" (Menü: / View / SQL Server Object Explorer)
  • Öffnen / erweitern Sie die Datenbank und ihre Tabellen
  • Klicken Sie mit der rechten Maustaste auf die Tabelle und wählen Sie "Daten anzeigen" aus dem Kontextmenü
  • Dies zeigt die Daten im Hauptbereich an
  • Optionaler Schritt: Klicken Sie auf das Filtersymbol "Datensatz sortieren und filtern" (das vierte Symbol von links in der Zeile über dem Ergebnis) und wenden Sie einen Filter auf eine oder mehrere Spalten an
  • Klicken Sie auf die Symbole "Script" oder "Script to File" (die Symbole rechts in der oberen Reihe sehen aus wie kleine Papierblätter)

Dadurch werden die (bedingten) INSERT-Anweisungen für die ausgewählte Tabelle im aktiven Fenster oder in der aktiven Datei erstellt.


Die Schaltflächen "Filter" und "Skript" Visual Studio 2013 :



Ich benutze sqlite, um dies zu tun. Ich finde es sehr, sehr nützlich für die Erstellung von Scratch / Test-Datenbanken.

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql


Mein Beitrag zu diesem Problem ist ein Powershell-INSERT-Skriptgenerator, mit dem Sie mehrere Tabellen schreiben können, ohne die umständliche SSMS-GUI verwenden zu müssen. Ideal für schnell persistente "Seed" -Daten in der Quellcodeverwaltung.

  1. Speichern Sie das folgende Skript als "Dateiname.ps1".
  2. Nehmen Sie Ihre eigenen Änderungen an den Bereichen unter "FERTIGEN SIE MICH".
  3. Sie können die Liste der Tabellen in beliebiger Reihenfolge zum Skript hinzufügen.
  4. Sie können das Skript in Powershell ISE öffnen und die Schaltfläche "Ausführen" drücken oder das Skript einfach in der Powershell-Eingabeaufforderung ausführen.

Standardmäßig ist das generierte INSERT-Skript "SeedData.sql" im selben Ordner wie das Skript.

Sie benötigen die SQL Server Management Objects-Assemblys, die installiert sein müssen. Diese sollten vorhanden sein, wenn Sie SSMS installiert haben.

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()

GenerateData ist ein erstaunliches Werkzeug dafür. Es ist auch sehr einfach, Änderungen daran vorzunehmen, weil der Quellcode für Sie verfügbar ist. Ein paar nette Features:

  • Namensgenerator für Namen und Orte von Personen
  • Möglichkeit, das Generierungsprofil zu speichern (nachdem es heruntergeladen und lokal eingerichtet wurde)
  • Möglichkeit, die Generierung über Skripts anzupassen und zu manipulieren
  • Viele verschiedene Ausgaben (CSV, Javascript, JSON, etc.) für die Daten (falls Sie das Set in verschiedenen Umgebungen testen müssen und den Datenbankzugriff überspringen möchten)
  • Kostenlos . Aber denken Sie daran zu spenden, wenn Sie die Software nützlich finden :).


Ich habe dieses script das ich in meinen Blog geschrieben habe ( How-to Insert-Anweisungsprozeduren auf SQL-Server generieren ).

Bis jetzt hat es für mich funktioniert, obwohl es sich um Bugs handeln könnte, die ich noch nicht entdeckt habe.





code-generation