Geeks With Blogs

AzamSharp Some day I will know everything. I hope that day never comes.
SqlBulkCopy class in Asp.net 2.0 provides functionality to copy data from one table to another table. This transfer of data is very easy to perform. Check out the following code:
 

string connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
        SqlConnection myConnection = new SqlConnection(connectionString); 
        SqlCommand myCommand = new SqlCommand("SELECT * FROM Person",myConnection);
        myConnection.Open();
        SqlDataReader dr = myCommand.ExecuteReader();
        
        SqlConnection myNewConnection = new SqlConnection(connectionString);
        myNewConnection.Open(); 
        SqlBulkCopy bulk = new SqlBulkCopy(myNewConnection);
        bulk.DestinationTableName = "[Person2]";
                          
   
        bulk.WriteToServer(dr);
        myNewConnection.Close();
        dr.Close();
        myConnection.Close();
        bulk.Close(); 

Check out the complete article Using SqlBulkCopy class in Asp.net 2.0 Posted on Monday, July 18, 2005 8:22 PM | Back to top


Comments on this post: Using SqlBulkCopy class to copy data from one table to another

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
Thats easy?

If you want to copy data why not do this by calling one stored procedure and INSERT INTO ? Or select into a destination table?

1 line of code vs 15 lines of code, bad example.

Left by JP on Jul 19, 2005 1:12 PM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
This example only shows that IF you want to carry out this task using C# from your code. Offcourse you can use Sql Queries and Stored procedures to perform this action. But remember that most of the time inserting the records using SqlBulkCopy class is much faster than other ways listed.
Left by AzamSharp on Jul 19, 2005 1:42 PM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
JP,

Do you know of a way using just INSERT INTO SELECT to bypass the need to do what error message 8101 says?

Using SqlBulkCopy, you can get around this because it does all that for you, but I wondered if you know a way using plain T-SQL? Thanks

Error 8101
Severity Level 16
Message Text
An explicit value for the identity column in table '%.*ls' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Explanation
You have attempted to insert a row containing a specific identity value into a table that contains an identity column. However, you did not provide a column list or have SET IDENTITY_INSERT enabled for the specified table.

Action
To insert a specific identity row in a table containing an identity column successfully you must provide a column list and SET IDENTITY_INSERT to ON. The following example inserts identity row 2, where iID is defined as the identity column.



Left by Josh on Sep 30, 2005 9:01 AM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
I found this a great way to insert massive amounts of records parsed from text files into my database. So in that case, using tsql for the bulk copy was not an option. SqlBulkCopy was the perfect solution.
Left by DotNetManiac on Apr 12, 2006 6:35 AM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
I think SqlBulkCopy has some great applications. I just wrote an article on using it to batch import a bunch of data from Excel into SQL.

http://www.codescene.com/2006/06/importing_excel_into_sql_using.php
Left by Jay Buys on Jun 26, 2006 3:38 PM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
Did anyone try to bulkcopy tables from different databases and different cultureID?? it seems to be very tricky!!!!
Left by Jose Hevia on Sep 15, 2006 3:57 AM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
I'm getting an error about converting from a string to an int when I execute WriteToServer, but I don't know any semi-easy way to view the contents of my DataTable, is there one?
Left by Alex Bransky on Apr 17, 2007 11:34 AM

# re: Using SqlBulkCopy class to copy data from one table to another
Requesting Gravatar...
Forget it, I just discovered the visualizer that VS 2005 gives you in debug mode when you click on the magnifying glass. Better than nothing, but quite a memory hog.
Left by Alex Bransky on Apr 17, 2007 12:16 PM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net