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