June 14, 2005 at 1:11 am
Hi there,
I need to export som scores of tables to txtfiles, I have to do this table by table if I do this via EM. I am aware one could built a DTS package to handle variablenames, but this will take time. Is there a way, a tool maybe, to export more than 1 table to a txtfile?
Greetz,
Hans Brouwer
June 14, 2005 at 3:24 pm
Hi,
The best way will be DTS. The "quick" way is to script a list of bcp commands, see BCP utility syntax in BOL. You may use a query towards sysobjects to get table names.
For example (BOL Example) you want a series of bcp commands like this:
bcp "Northwind.dbo.mytable1" out "table1.txt" <other parameters here>
You may script something like that and run it once in Query Analyzer:
Select 'bcp "Northwind.dbo.' + Name + '" out "' + Name +'.txt" <other parameters>'
From Sysobjects where xtype ='u'
It will return rows that you may try to run as a batch file.
bcp "Northwind.dbo.Orders" out "Orders.txt" <other parameters>
bcp "Northwind.dbo.Products" out "Products.txt" <other parameters>
Do not forget to substitute <other parameters for real BCP utility parameters.
Regards,Yelena Varsha
June 15, 2005 at 12:06 pm
the following stored procedure will bcp out all user tables to separate text files with timestamp as part the file name
-- BCP out all user tables
-- Author G. Hanson
-- Date Added 02/23/2005
-- Last Changed 03/16/2005
--
-- Syntax usp_BCP_out_AllTables 'database','path for datafiles','sql server name','table name prefix'
--
-- Notes all data output files are tablenameyyyymmddhhss.dat
-- Change History
-- 03/16/2005 add table name prefix
CREATE PROCEDURE usp_BCP_out_AllTables
@dbname varchar(30),
@path varchar(50) = "C:\Temp",
@server varchar(50) = "biwgdcdev01\biwgdcdev01",
@tblprefix varchar(128) = "all"
AS
SET NOCOUNT ON
DECLARE @tablename varchar(30)
DECLARE @cmdline varchar(255)
DECLARE @ssql varchar(255)
DECLARE @tabcount smallint
DECLARE @today char(14)
set @today =
substring(convert(char(20),getdate(),20),1,4)+
substring(convert(char(20),getdate(),20),6,2)+
substring(convert(char(20),getdate(),20),9,2)+
substring(convert(char(20),getdate(),20),12,2)+
substring(convert(char(20),getdate(),20),15,2)+
substring(convert(char(20),getdate(),20),18,2)
SELECT @tabcount = 0
EXEC ('USE ' + @dbname)
create table #dumptables ([name] varchar(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' +
@dbname +
'..sysobjects where type = ' +
'''U'''
If @tblprefix = 'all'
Begin
Set @ssql = @ssql +
' and name <> ' +
'''dtproperties'''
End
Else
Begin
Set @ssql = @ssql +
' and name like ' +
char(39) + @tblprefix + '%' + char(39)
End
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 */
-- Add "-S<servername>" for a remoteserver, terminator used = ~ (tilde), specify terminator after '-t', '-T' is used for trusted connection,
-- use -U<username> -P<password> for standard security
SELECT @cmdline = 'bcp ' + @dbname + '..' + @tablename + ' out ' + @path + '\' + @tablename + @today +'.dat -c -t -T -S' + @server
print @server
print @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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply