CODEWITHSUNDEEP
c#.netsqlsql-server-2005
Robbert Dam
Robbert Dam

Reputation: 4107

Fast way of writing SQL server table into file

I have a remote SQL server. I want to make a local copy of all tables in this server. I don't care about the file format used locally, I'm looking for the fastest approach of getting the data from SQL server into the file. (note: server side backup is not an option)

This is my current approach:

Step 1. Creating a reader and read all data into a List of objects

while (reader.Read()) {

    var fieldCount = reader.FieldCount;
    for (int i = 0; i < fieldCount; i++) {
        objects.Add(reader.GetValue(i));
    }
}

Step 2. Convert the objects to strings

List<string> test = new List<string>();
foreach (var o in objects) {
    test.Add(o.ToString());
}

Step 3. Write the string to a (CSV) file

foreach (var s in test) {
    backupFile.Write("\"");
    backupFile.Write(s);
    backupFile.Write("\";");
}

I've measured the performance of these 3 steps:

I'm looking for a way to speed up step 2. Is there a faster way of getting these objects to a file? (doesn't have to be a text file. Binary of local database file is also ok)

Upvotes: 1

Views: 2218

Answers (3)

coltech
coltech

Reputation: 389

Use the OPENROWSET command. Example:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\testing.xls;', 
'SELECT Name, Date FROM [Sheet1$]') 
SELECT [Name], GETDATE() FROM MyTable

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52655

Why are you bothering with three steps exactly.

Why not

while (reader.Read()) {

    var fieldCount = reader.FieldCount;
    for (int i = 0; i < fieldCount; i++) {
           backupFile.Write("\"");
           backupFile.Write(reader.GetValue(i).ToString());
           backupFile.Write("\";");;
    }
    backupFile.WriteLine();
 }

Unless of course you're using two threads. One that pushes the data into a collection. And one that flushes the data into a file.

Upvotes: 3

Diego
Diego

Reputation: 36146

If you dont care about the file format used locally, how About loading your SQL table to a DataTable object and use:

datatable.WriteXml("c:\YourFile.xml");

Upvotes: 0

Related Questions