Performant Excel-Daten in SQL-Server 2005 speichern

06/10/2007 - 09:35 von Volker | Report spam
Hallo,

ich muß für ein kleines Projekt aus verschiedenen Excel-Dateien, Daten
"extrahieren" und zur weiteren Bearbeitung auf einen SQL-Servr 2005
speichern. Das Extrahieren der Daten funktioniert auch prima und muß
nicht diskutiert werden, einzig das Speichern der Daten soll hier
Thema sein.

Bis dato, haben wir die Daten in eine Access-DB (über DAO)
gespeichert, da aber die nachfolgende Bearbeitung der Daten zu
langwierig in Access ist, müssen die Daten nun auf den SQL-Server
2005.

Nur ... wie speichere ich die Daten performant in die SQL-Server-
Datenbank? Bei Access wurden 500.000 - 7.000.000 Datensàtze in einer
Zeit bis 15 Min. weggespeichert. Das ist die Zeit wo ich beim SQL-
Server 2005 auch in etwa hin möchte. Allerdings bin ich nicht so der
ADO-Experte daher mein Code der zwar schon funktioniert aber bestimmt
noch nicht so optimal ist.


Public Sub verbinden()
Dim cnPubs As ADODB.Connection
Dim strSQL As String
Dim timStart As Date
Set cnPubs = New ADODB.Connection
strSQL = "Driver={SQL Native
Client};ServerÛServer;Database=Datenbank;Uid=Benutzer;Pwd=Passwort;"
cnPubs.Open strSQL
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "Tabelle1", cnPubs, adOpenStatic, adLockBatchOptimistic
timStart = Now
For i = 1 To 6000000
.AddNew
.Fields("F1") = "F1"
.Fields("F2") = "F2"
.Fields("F3") = "F3"
.Fields("F4") = "F4"
.Fields("F5") = i
.Fields("F6") = "Test"
DoEvents
.MoveNext
If i Mod 100000 = 0 Then
.UpdateBatch
Debug.Print i
End If
Next i
.UpdateBatch
Debug.Print "Zeit:" & CStr(DateDiff("s", timStart, Now)) & "s"
.Close
End With
cnPubs.Close
Set rs = Nothing
Set cnPubs = Nothing

End Sub


Gruß
Volker

PS Der Code ist meinem Excel-VBA-Makro entnommen sollte aber auch
prima in VB (Classic9 kompilieren).
 

Lesen sie die antworten

#1 Berthold Neumann
06/10/2007 - 09:56 | Warnen spam
Moin Volker,

Bei 7 Mio. Datensàtzen gehe ich mal vom Einsatz einer Standard-Edition
(also kein Express) aus.
Dann stehen auch die SSIS zur Verfügung und damit wird die
Aufgabenstellung ganz einfach:

- SSIS-Paket mit einer Datenflusstask erstellen
- Exceldatenquelle wàhlen
- OLE/DB-Datenziel wàhlen
- Los gehts

Soll die Lösung dagegen auf einer Express-Edition laufen, dann bist Du
hier im falschen Board. Denn in diesem Fall ist es eher eine
VB/Excel-Fragestellung.

Gruß

Berthold



Am 06.10.2007, 09:35 Uhr, schrieb Volker :

Hallo,

ich muß für ein kleines Projekt aus verschiedenen Excel-Dateien, Daten
"extrahieren" und zur weiteren Bearbeitung auf einen SQL-Servr 2005
speichern. Das Extrahieren der Daten funktioniert auch prima und muß
nicht diskutiert werden, einzig das Speichern der Daten soll hier
Thema sein.

Bis dato, haben wir die Daten in eine Access-DB (über DAO)
gespeichert, da aber die nachfolgende Bearbeitung der Daten zu
langwierig in Access ist, müssen die Daten nun auf den SQL-Server
2005.

Nur ... wie speichere ich die Daten performant in die SQL-Server-
Datenbank? Bei Access wurden 500.000 - 7.000.000 Datensàtze in einer
Zeit bis 15 Min. weggespeichert. Das ist die Zeit wo ich beim SQL-
Server 2005 auch in etwa hin möchte. Allerdings bin ich nicht so der
ADO-Experte daher mein Code der zwar schon funktioniert aber bestimmt
noch nicht so optimal ist.


Public Sub verbinden()
Dim cnPubs As ADODB.Connection
Dim strSQL As String
Dim timStart As Date
Set cnPubs = New ADODB.Connection
strSQL = "Driver={SQL Native
Client};ServerÛServer;Database=Datenbank;Uid=Benutzer;Pwd=Passwort;"
cnPubs.Open strSQL
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "Tabelle1", cnPubs, adOpenStatic, adLockBatchOptimistic
timStart = Now
For i = 1 To 6000000
.AddNew
.Fields("F1") = "F1"
.Fields("F2") = "F2"
.Fields("F3") = "F3"
.Fields("F4") = "F4"
.Fields("F5") = i
.Fields("F6") = "Test"
DoEvents
.MoveNext
If i Mod 100000 = 0 Then
.UpdateBatch
Debug.Print i
End If
Next i
.UpdateBatch
Debug.Print "Zeit:" & CStr(DateDiff("s", timStart, Now)) & "s"
.Close
End With
cnPubs.Close
Set rs = Nothing
Set cnPubs = Nothing

End Sub


Gruß
Volker

PS Der Code ist meinem Excel-VBA-Makro entnommen sollte aber auch
prima in VB (Classic9 kompilieren).






Berthold Neumann
http://www.sql-ssis.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

Ähnliche fragen