BCP Extras

Here are two stored procedures that can automate the entire exporting and importing of tables in a database. They are especially useful when you need to move entire databases around but don't want to use dump/load database operations (such as when creating many test databases for programmers' unit testing, or restructuring physical file locations). Execute this using OSQL/ISQL/SQL Query Analyzer.

The first stored procedure is for BCPing OUT all tables in a database. It is named BCP_out_AllTables.

if exists(select name from sysobjects where name = 'BCP_out_AllTables') 
  begin
    drop procedure BCP_out_AllTables
  end
GO
CREATE PROCEDURE BCP_out_AllTables
    @dbname         varchar(30),
    @path            varchar(50) = "C:\Temp"
AS

SET NOCOUNT ON

DECLARE @tablename      varchar(30)
DECLARE @srvname        varchar(30)
DECLARE @cmdline        varchar(125)
DECLARE @ssql           varchar(255)
DECLARE @tabcount        smallint

SELECT @tabcount = 0
select @srvname=@@servername
EXEC ('USE ' + @dbname)

create table #dumptables ([name] varchar(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname + '..sysobjects
 where type = ''U'''
exec (@ssql)

DECLARE cnames     CURSOR FOR
select [name] from #dumptables

OPEN cnames

FETCH NEXT FROM cnames INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status = -2)
    BEGIN
        FETCH NEXT FROM cnames INTO @tablename
        CONTINUE
    END
    PRINT 'Exporting table: ' + @tablename
    /* build commandline */
    /* '-T' is used for trusted connection,  */
    /*  or '-U and -P switches for sql connections */

SELECT @cmdline = 'bcp "' + @dbname + '..[' + @tablename + ']" out "' +
         @path + '\' + @tablename + '.dat" -c  -Usa -P -S ' + @srvname
    PRINT 'Executing: ' + @cmdline
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
    SELECT @tabcount = @tabcount + 1
    FETCH NEXT FROM cnames INTO @tablename
END
DEALLOCATE cnames
/* Print usermessage */
SELECT CONVERT(varchar(10),@tabcount) + ' tables from database '+ @dbname
        + ' exported to ' + @path
GO
sp_help "BCP_out_AllTables"
GO

After the stored procedure is defined, it can be executed easily:

exec BCP_out_AllTables "Northwind", "D:\Temp" 

This yields the following:

Exporting table: Orders 
Executing: bcp "northwind..[Orders]" out "D:\Temp\Orders.dat" -c
       -Usa -P -S C814\DBARCH01
Exporting table: Products
Executing: bcp "northwind..[Products]" out "D:\Temp\Products.dat" -c
       -Usa -P -S C814\DBARCH01
. . .
15 tables from database northwind exported to D:\Temp

The next stored procedure is for BCPing IN all tables in a database. It is named BCP_in_AllTables.

if exists(select name from sysobjects where name = 'BCP_in_AllTables') 
  begin
    drop procedure BCP_in_AllTables
  end
GO
CREATE PROCEDURE BCP_in_AllTables
    @dbname         varchar(30),
    @path            varchar(50) = "C:\Temp"
AS
SET NOCOUNT ON
DECLARE @tablename         varchar(30)
DECLARE @srvname         varchar(30)
DECLARE @cmdline        varchar(125)
DECLARE @ssql              varchar(255)
DECLARE @tabcount        smallint
SELECT @tabcount = 0
SELECT @srvname = @@servername
EXEC ('USE ' + @dbname)
create table #dumptables ([name] varchar(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname
        + '..sysobjects where type = ''U'''
exec (@ssql)
DECLARE cnames     CURSOR FOR
select [name] from #dumptables
OPEN cnames
FETCH NEXT FROM cnames INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status = -2)
    BEGIN
        FETCH NEXT FROM cnames INTO @tablename
        CONTINUE
    END
    PRINT 'Importing table: ' + @tablename
    /* build commandline */
    /* '-T' is used for trusted connection, */
    /*  or '-U and -P switches for sql connections */
SELECT @cmdline = 'bcp "' + @dbname + '..[' + @tablename + ']" in "'
       + @path + '\' + @tablename + '.dat" -c  -Usa -P -S ' + @srvname
    PRINT 'Executing: ' + @cmdline
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
    SELECT @tabcount = @tabcount + 1
    FETCH NEXT FROM cnames INTO @tablename
END
DEALLOCATE cnames
/* Print usermessage */
PRINT CONVERT(varchar(10),@tabcount) + ' files imported into database '
       + @dbname + '  from ' + @path
GO
sp_help "BCP_in_AllTables"
GO

After the stored procedure is defined, it can be executed easily:

exec BCP_in_AllTables "Northwind", "D:\Temp" 

This yields the following:

Importing table: Orders 
Executing: bcp "northwind..[Orders]" in "D:\Temp\Orders.dat" -c
       -Usa -P -S C814\DBARCH01
Importing table: Products
Executing: bcp "northwind..[Products]" in "D:\Temp\Products.dat" -c
       -Usa -P -S C814\DBARCH01
. . .
15 files imported into database northwind from D:\Temp