Importing and Exporting data to and from SQL Server.
I recently helped a friend out who only had access to SQL Server Management Studio Express, and he needed to copy a database locally from his PC to his remote web hosting company. Normally, the process is a simple backup/restore, but his hosting company does not allow restoring databases. Luckily, however, the company does allow direct access to his hosted database via client tools such as SSMS. Unluckily, SSMS Express does not include any tools that allow you to export data to a remote server.
So, here's what we did: First, we scripted out the entire database, including all tables, indexes,...
You're INSERTing multiple rows from an un-normalized import table. Each row is assigned an identity primary key. You know you can use scope_identity() to retrieve one identity at a time, but how do you retrieve a whole set of them? Are cursors the only answer? As usual, it depends on having logical specifications and a good design. read more..
In my SQLBulkCopy article, I mentioned that you can quickly copy data from anything that implements IDataReader to a SQL Server table using SQLBulkCopy (new in .NET 2.0). In this SQLTeam forum post, Jesse Hersch (jezemine) tells us that SQLBulkCopy only actually uses 3 methods of the interface. This means that it is very easy to quickly create your own custom class which implements this interface to bulk copy pretty much any type of object collection or array to a SQL Server table.
My latest article has just been posted over at SQLTeam.com:
Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server
.NET Framework 2.0 introduces a very handy new class in the System.Data.SqlClient namespace called SqlBulkCopy that makes it very easy and efficient to copy large amounts of data from your .NET applications to a SQL Server database. You can even use this class to write a short .NET application that can serve as a "middleman" to move data between database servers.
If you ever need to move large amounts of data to SQL Server from a .NET application, SQLBulkCopy...