It definitely helps me to understand about OpenDataSource. I'm working on a project to see how to create sample SQL databases (32bit/64bit SQL Server 2005/2008 including Express Edition) for a .NET application. I'm trying to figure out the following items:
1. How to create the databases?
2. Where to store the sample data?
3. How to import the data?
What are the pros and cons (especially what datatype cannot be imported) in each option? What data source should I create (e.g. text file, XML, Access database(MDB), Excel file (CVS)) if portability is a must? Ideally, the data importing can be initialized from a .NET 3.0/3.5 application instead of thru SSMS or command prompt.
Most of those questions revolve around exactly what you are trying to do, but for a few rules of thumb:
If you are moving a preexisting database to a test environment I would use backup and restore. It tends to be one of the easier ways and more complete ways. If you are creating something new or highly modified, I would use t-sql scripts as they will give you the most direct control.
2. How you export the data depends on who you are sending it to and what they can read. As a general rule of thumb, csv files are simultaneously the smallest way to store the data and one of the most widely accepted since they can be easily read by a variety of programs including straight text editors and they can be imported into almost anything. The downside of course is that they do not include within them information like data type or relations between groupings of files that can be more thoroughly expressed in a richer format. For a richer format, you may wish to use access files or create a sql backup and send the entire backup file, depending again on your exact intent.
3. When it works, bulk insert is almost always one of the fastest and most efficient ways of importing, but it only works on certain file types formatted in certain ways and it has very little fault tolerance. Also for rarely repeated imports, there is nothing wrong with just using the SSIS or DTS wizards. I tend to use opendatasource for a lot of the automated imports in procedures because it provides more flexibility and more fault tolerance than bulk insert while remaining pure T-SQL and not requiring outside programs.
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/