Although some simple databases can start off completely empty, in most cases you need to preload tables by using external data from flat files or other databases. Likewise, once your database has been in use for a while, you’ll probably need to transfer data to Microsoft Excel or some other program. SQL Server 2005 Express has several methods that you can use to import or export data. The primary tools that ship with SQL Server Express are the bcp utility, the T-SQL BULK INSERT statement, and the T-SQL OPENROWSET statement. Bcp is a command-line tool that can import and export files. Using bcp is somewhat archaic and can be cumbersome. But once you set up a transfer, it can be easily incorporated into your Windows command shell scripts so that you can rerun the data transfer.
BULK INSERT is a T-SQL statement that you can run from a T-SQL script by using SQL Server Management Studio Express (SSMSE) and Query Editor or the command-line sqlcmd program. As its name implies, you can use the BULK INSERT statement only to import data to SQL Server. OPENROWSET is another T-SQL statement that you can run from Query Editor or the sqlcmd utility. The OPENROWSET statement can query and update data from OLE DB data sources. OPENROWSET is best suited to transferring data to and from other databases.
However, for most SQL Server Express users, the best tool for the job is the SQL Server Import and Export Wizard (DTSWizard.exe), which is hidden deep in the C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ directory. Unlike the other command-oriented import and export tools, the SQL Server Import and Export Wizard is a fully graphical tool. The SQL Server Import and Export Wizard was added to SQL Server Express as part of Service Pack 1 (SP1), and it can work flat files as well as .NET, OLE DB, and ODBC data sources.
The SQL Server Import and Export Wizard provides powerful and flexible data transfer and transformation capabilities. But when you use it with SQL Server Express, the wizard is best suited to ad-hoc data transfers rather than transfers that need to be run regularly. This caveat exists because although the SQL Server Import and Export Wizard can save its transfer requests as SQL Server Integration Services (SSIS) packages, the wizard doesn’t include the full SSIS functionality. Still, if you’re looking for a powerful, easy-to-use data import and export utility, dig into the C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ directory and give the SQL Server Import and Export Wizard a try.
