Wie lassen sich INSERT-Anweisungen für eine SQL Server-Tabelle am besten automatisch generieren?

Question

Wir schreiben eine neue Anwendung, und während des Testens benötigen wir eine Reihe von Dummy-Daten. Ich habe diese Daten mithilfe von MS Access hinzugefügt, um Excel-Dateien in die entsprechenden Tabellen zu übertragen.

Von Zeit zu Zeit wollen wir die betreffenden Tabellen "auffrischen", was bedeutet, dass wir sie alle löschen, neu erstellen und eine gespeicherte MS Access-Anhangsabfrage ausführen.

Der erste Teil (Löschen und Neuanlegen) ist ein einfaches SQL-Skript, aber der letzte Teil lässt mich erschaudern. Ich möchte ein einziges Setup-Skript, das eine Reihe von INSERTs hat, um die Dummy-Daten zu regenerieren.

Ich habe die Daten jetzt in den Tabellen. Wie kann ich am besten automatisch eine große Liste von INSERT-Anweisungen aus diesem Datensatz erstellen?

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

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

Answer 1

Ich verwende SSMS 2008 Version 10.0.5500.0. In dieser Version gibt es im Assistenten zum Generieren von Skripten anstelle der Schaltfläche "Erweitert" den unten stehenden Bildschirm. In diesem Fall wollte ich nur die Daten einfügen und keine Anweisungen erstellen, also musste ich die beiden eingekreisten Eigenschaften ändern

Answer 2

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

Answer 3

Wenn Sie einen programmatischen Zugriff benötigen, können Sie eine Open-Source gespeicherte Prozedur "GenerateInsert" verwenden.

INSERT-Anweisung(en) Generator

Nur als einfaches und schnelles Beispiel, um INSERT-Anweisungen für eine Tabelle zu erzeugen AdventureWorks.Person.AddressType folgende Anweisungen ausführen:

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

Dadurch wird das folgende Skript erzeugt:

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

Answer 4

Mein Beitrag zu diesem Problem: ein Powershell INSERT-Skriptgenerator, mit dem Sie mehrere Tabellen skripten können, ohne die umständliche SSMS-GUI verwenden zu müssen. Hervorragend geeignet, um schnell "Seed"-Daten in der Versionskontrolle zu speichern.

1. Speichern Sie das folgende Skript als "Dateiname.ps1".
2. Nehmen Sie Ihre eigenen Änderungen in den Bereichen unter "CUSTOMIZE ME" vor.
3. Sie können die Liste der Tabellen dem Skript in beliebiger Reihenfolge hinzufügen.
4. Sie können das Skript in Powershell ISE öffnen und auf die Schaltfläche Abspielen klicken oder das Skript einfach in der Powershell-Eingabeaufforderung ausführen.

Standardmäßig wird das erzeugte INSERT-Skript "SeedData.sql" im selben Ordner wie das Skript gespeichert.

Sie benötigen die installierten SQL Server Management Objects-Assemblies, die bereits vorhanden sein sollten, 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()

Answer 5

Um eine INSERT-Anweisung mit gefiltertem Datensatz (WHERE QUERY) zu erhalten, können Sie

1. RECHTSKLICK AUF TABELLE SKRIPTTABELLE ALS > ERSTELLEN BIS > NEUE ABFRAGE

2. UMBENENNEN IN TEMP_TABLE

3. JETZT LAUFEN

   SELECT INTO TEMP_TABLE FROM YOURFIRST TABLE WO JETZT IHRE KRITERIEN HIER

Auf diese Weise enthält Ihre temporäre Tabelle nur die gewünschten Datensätze, Führen Sie nun den Skript-Assistenten aus, wie von @Mike Ritacco mit DATA ONLY erhalten Sie die genauen Einfügeanweisungen.