SqlBulkCopy 快速插入数据

[转]本文来自http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy(v=vs.80).aspx

此代码用于演示仅使用 SqlBulkCopy 的语法。如果源表和目标表都在同一个 SQL Server 实例中，则使用 Transact-SQL INSERT … SELECT 语句复制数据会更方便快捷。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

using System.Data.SqlClient;   class Program {     static void Main()     {         string connectionString = GetConnectionString();         // Open a sourceConnection to the AdventureWorks database.         using (SqlConnection sourceConnection =                    new SqlConnection(connectionString))         {             sourceConnection.Open();               // Perform an initial count on the destination table.             SqlCommand commandRowCount = new SqlCommand(                 "SELECT COUNT(*) FROM " +                 "dbo.BulkCopyDemoMatchingColumns;",                 sourceConnection);             long countStart = System.Convert.ToInt32(                 commandRowCount.ExecuteScalar());             Console.WriteLine("Starting row count = {0}", countStart);               // Get data from the source table as a SqlDataReader.             SqlCommand commandSourceData = new SqlCommand(                 "SELECT ProductID, Name, " +                 "ProductNumber " +                 "FROM Production.Product;", sourceConnection);             SqlDataReader reader =                 commandSourceData.ExecuteReader();               // Open the destination connection. In the real world you would             // not use SqlBulkCopy to move data from one table to the other             // in the same database. This is for demonstration purposes only.             using (SqlConnection destinationConnection =                        new SqlConnection(connectionString))             {                 destinationConnection.Open();                   // Set up the bulk copy object.                 // Note that the column positions in the source                 // data reader match the column positions in                 // the destination table so there is no need to                 // map columns.                 using (SqlBulkCopy bulkCopy =                            new SqlBulkCopy(destinationConnection))                 {                     bulkCopy.DestinationTableName =                         "dbo.BulkCopyDemoMatchingColumns";                       try                     {                         // Write from the source to the destination.                         bulkCopy.WriteToServer(reader);                     }                     catch (Exception ex)                     {                         Console.WriteLine(ex.Message);                     }                     finally                     {                         // Close the SqlDataReader. The SqlBulkCopy                         // object is automatically closed at the end                         // of the using block.                         reader.Close();                     }                 }                   // Perform a final count on the destination                 // table to see how many rows were added.                 long countEnd = System.Convert.ToInt32(                     commandRowCount.ExecuteScalar());                 Console.WriteLine("Ending row count = {0}", countEnd);                 Console.WriteLine("{0} rows were added.", countEnd - countStart);                 Console.WriteLine("Press Enter to finish.");                 Console.ReadLine();             }         }     }       private static string GetConnectionString()         // To avoid storing the sourceConnection string in your code,         // you can retrieve it from a configuration file.     {         return "Data Source=(local); " +             " Integrated Security=true;" +             "Initial Catalog=AdventureWorks;";     } }