Stored Procedures Work in Concert to Import and Export Data

Microsoft SQL Server administrators often have to import and export data between a database and a .dat file. There are many different ways to automate an import or export task. For example, you might use T-SQL's BULK INSERT statement or SQL Server's bulk copy program (Bcp) in a script or construct a DTS package. No matter which method you choose, your script or package must provide specific table names and filenames.

I've created a simple yet generic solution for exporting or importing data that automatically obtains the table names and filenames. This solution consists of two stored procedures: BCPObject and ExpOrImpDB. BCPObject performs the actual import or export task. ExpOrImpDB obtains the table names and filenames that BCPObject needs to perform the import or export task. To understand how this solution works, it's best to first look at how BCPObject works.

BCPObject
As Listing 1 shows, BCPObject is short and straightforward. It uses Bcp to import and export data. Although I designed BCPObject to work with ExpOrImpDB, you can use BCPObject by itself for simple import or export tasks. However, you must provide the table name, filename, and some other information as parameters. In all, BCPObject takes five parameters:

For example, suppose you want to export the contents of the Products table in the Northwind database to an @-separated file called C:tempprod.dat. In this case, you'd execute the BCPObject procedure with the statement

EXEC BCPObject 'Northwind',   Products',  'C:tempprod.dat',   'OUT', '@' 

Now let's say you want to create in the Pubs database an empty table called newProds that has the same structure as the Products table in the Northwind database, then import the C:tempprod.dat file's contents into the newProds table. To do so, you'd run the code

USE pubs GO SELECT * INTO newProds FROM   Northwind..Products WHERE 0=1 USE master GO EXEC BCPObject 'Pubs',   'newProds', 'C:tempprod.dat',   'IN', '@' 

Note that the directory must exist prior to the BCPObject procedure's execution. In export mode, BCPObject creates the file, whereas in import mode, BCPObject assumes existence of the file. Also note that adding the WHERE 0=1 condition to the SELECT * INTO statement causes the table to be created empty. The table is created empty before BCPObject's execution to make sure that, after the stored procedure runs, the table is filled.

BCPObject uses an administrative trusted connection. In other words, to use the procedure, you must log on to Windows as a member of an Administrators group and have the sysAdmin role in SQL Server. In addition, all the tables must hold Primitive-type data or user-defined data types based on Primitive types. In other words, the tables can't contain image, text, xml, or varbinary(max) columns.

Although useful for importing and exporting data, BCPObject has the same drawback of BULK INSERT and the other tools mentioned: You must provide the table name and filename. This is where the ExpOrImpDB procedure comes in handy.

ExpOrImpDB
ExpOrImpDB comes in handy when you have to import data to or export data from all the tables in a database because it automatically provides the names of the tables and files. You don't need to provide this information.

ExpOrImpDB takes four parameters:

For example, if you call ExpOrImpDB with the statement

exec ExpOrImpDB 'testDB',   'C:tempbackup', 'OUT', '!' 

ExpOrImpDB will export the data from all the tables in the testDB database to the C:tempbackup folder and will use an exclamation point (!) as the column separator.

Listing 2 shows ExpOrImpDB. This stored procedure creates a temporary table, then retrieves the names of all the tables in the specified database. For each table, ExpOrImpDB performs three steps. First, it creates a filename from the table name, adding a .dat extension. Second, ExpOrImp-DB combines the name of the specified directory folder with the filename to create the full path to the .dat file. Finally, ExpOrImpDB calls BCPObject, which runs Bcp to export or import the data. To successfully export or import all the tables, none of the tables can have an image or text column. In addition, the database user must own all the tables.

To run ExpOrImpDB, you must be logged on to Windows at least as a db_owner. In other words, you have been granted access to SQL Server and have permission to perform configuration and maintenance activities on the target databases. An alternative is to log on to Windows as a member of an Administrators group and have the sysAdmin role in SQL Server.

I created and tested ExpOrImpDB and BCPObject on SQL Server 2000 Standard Edition, Service Pack 1 (SP1). It should also work on SQL Server 7.0.

A Handy Tool
You can use ExpOrImpDB to easily export or import data from SQL Server tables consisting of simple data-type columns. This stored procedure is especially handy when you must quickly export SQL Server data to non-SQL Server environments. It also comes in handy when you need to quickly import data from non-SQL Server environments into SQL Server databases.