Wie behält man die Zeilenreihenfolge mit SqlBulkCopy bei?

Question

Ich exportiere Daten programmatisch aus Excel in SQL Server 2005 mit SqlBulkCopy. Das einzige Problem, das ich habe, ist, dass die Zeilenreihenfolge in der Excel-Datei nicht beibehalten wird. Ich habe keine Spalte, nach der ich ordnen muss, ich möchte nur, dass die Datensätze in der gleichen Reihenfolge eingefügt werden, in der sie im Excel-Arbeitsblatt erscheinen.

Ich kann die Excel-Datei nicht ändern und muss mit dem arbeiten, was ich habe. Wenn ich nach einer der vorhandenen Spalten sortiere, wird die Reihenfolge unterbrochen.

Bitte um Hilfe.

P.S. Am Ende fügte ich die ID-Spalte in die Tabelle ein, aber es gibt wohl keine Möglichkeit, die Reihenfolge beim Export/Import beizubehalten.

Answer 1

Ich glaube nicht, dass die Reihenfolge der Zeilen durch SQL festgelegt oder garantiert wird, es sei denn, Sie verwenden eine "ORDER BY"-Klausel.

Aus einem Beitrag von Bill Vaughn ( http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html ) :

Bestellung verwenden von: Auch wenn eine Tabelle einen einen geclusterten Index hat (der die Daten in physischer Reihenfolge speichert), garantiert SQL Server nicht garantieren, dass die Zeilen in in dieser (oder einer bestimmten) Reihenfolge zurückgegeben werden, es sei denn, eine ORDER BY Klausel ist verwendet wird.

Ein weiterer Link mit Informationen:

http://sqlblogcasts.com/blogs/simons/archive/2007/08/21/What-is-the-position-of-a-row--.aspx

Answer 2

Nach vielen Recherchen scheint es offensichtlich zu sein, dass es keine Möglichkeit gibt, die Zeilenreihenfolge mit dem Befehl "Bulk Insert", wie er von Microsoft angeboten wird, beizubehalten. Sie müssen entweder selbst eine ID-Spalte direkt in die Importdatei einfügen, eine Shell oder ein anderes externes Skript verwenden, oder Sie verzichten darauf. Es scheint, dass dies eine notwendige (und einfache) Funktion für Microsoft wäre, aber nach mehr als einem Jahrzehnt, in dem Microsoft nichts getan hat, wird das nicht passieren.

Ich musste jedoch die tatsächliche Reihenfolge der Datensätze in der Importdatei nach dem Import beibehalten, da die höheren Datensätze die niedrigeren verdrängen würden, wenn eine bestimmte Spalte denselben Wert hätte.

Also habe ich einen anderen Weg gewählt. Meine Zwänge waren:

• Ich konnte die Quelldatei überhaupt nicht ändern. (und einen schlechten Präzedenzfall geschaffen!)
• Ich konnte kein externes Skript verwenden. Zu kompliziert. Es musste eine einfache T-Sql-basierte Lösung sein, keine CMD-Ausführungen. Das Ganze musste in einer einzigen Prozedur ablaufen, damit es automatisiert werden konnte.

Mir gefiel die Logik der Verwendung von Powershell, um geordnete Einfügeanweisungen für jede Zeile zu erstellen, die dann in Sql ausgeführt werden. Im Wesentlichen wurde jeder Datensatz in eine Warteschlange für die individuelle Einfügung und nicht für die Masseneinfügung gestellt. Ja, es würde funktionieren, aber es wäre auch sehr langsam. Ich habe oft Dateien mit mehr als 500.000 Zeilen in ihnen. Ich brauchte etwas SCHNELLES.

So bin ich auf XML gestoßen. Bulk-Upload der Datei direkt in eine einzige XML-Variable. Dadurch würde die Reihenfolge der Datensätze beibehalten, wenn sie der XML-Datei hinzugefügt werden. Dann parsen Sie die XML-Variable und fügen die Ergebnisse in eine Tabelle ein, wobei Sie gleichzeitig eine Identitätsspalte hinzufügen.

Es wird davon ausgegangen, dass es sich bei der Importdatei um eine Standardtextdatei handelt, bei der jeder Datensatz mit einem Zeilenvorschub (Char(13)+Char(10)) endet.

Mein Ansatz besteht aus 2 Schritten:

1. Führen Sie die SQL-Anweisung IMPORT (mit OPENROWSET) aus und kapseln Sie jeden Datensatz mit XML-Tags ein. Erfassen Sie die Ergebnisse in einer XML-Variablen.

2. Parsen Sie die Variable anhand der XML-Tags in eine Tabelle und fügen Sie eine aufsteigende [ID]-Spalte hinzu.

   ---------------------------------
   Declare @X xml;
   ---------------------------------
   SELECT @X=Cast('<X>'+Replace([BulkColumn],Char(13)+Char(10),'</X><X>')+'</X>' as XML)
   FROM OPENROWSET (BULK N'\\FileServer\ImportFolder\ImportFile_20170120.csv',SINGLE_CLOB) T
   ---------------------------------
   SELECT [Record].[X].query('.').value('.','varchar(max)') [Record]
   ,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [ID]
   --Into #TEMP 
   FROM @X.nodes('X') [Record](X);
   ---------------------------------

   • Die XML-Tags ersetzen jeden Zeilenvorschub.

   • Wenn die Datei mit einem Zeilenvorschub endet, wird am Ende eine Leerzeile eingefügt. Löschen Sie einfach die letzte Zeile.

Ich schrieb dies in meine Prozedur mit dynamischen Sql, so dass ich in den FileName übergeben konnte und die ID auf 1 oder 0 (für den Fall, dass es eine Kopfzeile) beginnen.

Ich konnte dies mit einer Datei mit 300K Datensätzen in etwa 5 Sekunden durchführen.

Answer 3

Wenn Sie die Excel-Tabelle als CSV-Datei speichern können, ist es sehr einfach, mit einer beliebigen Skriptsprache eine Liste von INSERT-Anweisungen zu erstellen, die in genau der gleichen Reihenfolge wie die Tabelle ausgeführt werden. Hier ist ein schnelles Beispiel in Groovy, aber jede Skriptsprache kann das genauso gut, wenn nicht sogar einfacher:

def file1 = new File('c:\\temp\\yourSpreadsheet.csv')
def file2 = new File('c:\\temp\\yourInsertScript.sql')

def reader = new FileReader(file1)
def writer = new FileWriter(file2)

reader.transformLine(writer) { line ->
    fields =  line.split(',')

    text = """INSERT INTO table1 (col1, col2, col3) VALUES ('${fields[0]}', '${fields[1]}', '${fields[2]}');"""

}

Sie können dann Ihr "yourInsertScript.sql" in Ihrer Datenbank ausführen, und Ihre Bestellung wird mit der in Ihrer Tabelle übereinstimmen.

Answer 4

Möglicherweise können Sie auch eine Identitätsspalte in Ihrer Tabelle definieren, die sich beim Laden der Daten automatisch erhöht. Auf diese Weise können Sie später danach sortieren, wenn Sie die Datensätze wieder in der gleichen Reihenfolge haben möchten.