April 19, 2005 at 3:20 am
Hi!
I have three questions:
1) How do I export table data to textfiles with filename the same as tablename in an easy way.
I have 40-50 tables that I need to export. The exporting wizard only seems to take one destination file.
2) I also want the files to be in format ISO-8859-1.
3) Large textfields (some are several pages long) should be put in separate files
Running SQL Server 2000
Any help appreciated
/Henrik
April 20, 2005 at 1:00 am
Hi,
For 1) and 2), you could use a script like below. For 3), you should give some more details about what you want exactly. You can put the below script in a stored procedure, and schedule it as a job. For the current script to run, the destination folder should exists.
Bert
/* The script */
declare @tbl varchar(255), @filename varchar(255),@bcp varchar(1024)
declare tbl cursor
local FAST_FORWARD
for
-- select here the table names and the filenames to export
select filename = 'D:\temp\' + db_name() + '\' + name + '.txt' ,
tblname = db_name()+ '.' + user_name(uid) + '.' + name
from sysobjects
where type = 'U'
open tbl
goto nexttbl
while @@fetch_status = 0
begin
-- bcp options :
-- -q : to allow blancs and other special characters in the @tbl
-- -c : bulc copy using \t as coluln delimiter and \n as row delimiter
-- -C ACP : use the ISO-8859-1 charset
-- -T : use trusted connections
set @bcp = 'bcp "' + @tbl + '" out "' + @filename + '" -q -c -C ACP -T'
exec master.dbo.xp_cmdshell @bcp
nexttbl:
fetch tbl into @filename,@tbl
end
close tbl
deallocate tbl
April 20, 2005 at 1:51 am
Thanks for the reply!
Another thing: Can I get fixed length on the fields?
April 20, 2005 at 3:24 am
Yes you can, but you have to create a format file for the bcp. If you want to continue using SQL script, you can try following script ( it uses a stored procedure ). Adapt to your needs ...
create procedure dbo.usp_getformatfile
(
@TABLE_CATALOG nvarchar(128),
@TABLE_SCHEMA nvarchar(128),
@TABLE_NAME sysname
)
as
begin
-- this procedure generates a result set
-- for a format file with fixed length
--
-- a format file should start with version and number of columns
-- bcp does not allow multiple result sets, so the first 2 lines of the
-- format file should be generated otherwise
set nocount on
declare @nbfields int
select @nbfields = max(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TABLE_NAME
and TABLE_SCHEMA = @TABLE_SCHEMA
and TABLE_CATALOG = @TABLE_CATALOG
select [Host file field order] = ORDINAL_POSITION,
[Host file data type] = 'SQLCHAR',
[Prefix length] = 0,
[Host file data length] = case when CHARACTER_MAXIMUM_LENGTH is not null then CHARACTER_MAXIMUM_LENGTH else NUMERIC_PRECISION + 1 end,
[Terminator] = case when ORDINAL_POSITION = @nbfields then '"\n"' else '""' end,
[Server column order] = ORDINAL_POSITION,
[Server column name] = COLUMN_NAME,
[Collation] = 'Latin1_General_BIN'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TABLE_NAME
and TABLE_SCHEMA = @TABLE_SCHEMA
and TABLE_CATALOG = @TABLE_CATALOG
order by ORDINAL_POSITION
end
go
declare
@filename varchar(255),
@formatfilename varchar(255),
@bcp varchar(1024),
@sql varchar(2048),
@xpcmd varchar(1024),
@nbfields int
declare
@TABLE_CATALOG nvarchar(128),
@TABLE_SCHEMA nvarchar(128),
@TABLE_NAME sysname
declare tbl cursor
local FAST_FORWARD
for
-- select here the tables to export
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
open tbl
goto next_tbl
while @@fetch_status = 0
begin
-- create the format file
set @formatfilename = 'd:\temp\' + db_name() + '\fmt_' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '.txt'
set @filename = 'd:\temp\' + db_name() + '\' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '.txt'
-- first 2 lines of the format file
set @xpcmd = 'echo 8.0 > "' + @formatfilename + '"'
exec master.dbo.xp_cmdshell @xpcmd, no_output
select @nbfields = max(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TABLE_NAME
and TABLE_SCHEMA = @TABLE_SCHEMA
and TABLE_CATALOG = @TABLE_CATALOG
set @xpcmd = 'echo ' + convert(varchar(12),@nbfields) + ' >> "' + @formatfilename + '"'
exec master.dbo.xp_cmdshell @xpcmd, no_output
-- rest of the lines. Save in an other file
set @sql = 'exec ' + db_name() + '.dbo.usp_getformatfile '
+ ' @TABLE_CATALOG = ''' + @TABLE_CATALOG + ''''
+ ',@TABLE_SCHEMA = ''' + @TABLE_SCHEMA + ''''
+ ',@TABLE_NAME = ''' + @TABLE_NAME + ''''
set @bcp = 'bcp "' + @sql + '" queryout "' + @formatfilename + '_" -q -c -C ACP -T'
--print @bcp
exec master.dbo.xp_cmdshell @bcp, no_output
-- concat the 2 files into 1
set @xpcmd = 'COPY /Y "' + @formatfilename + '" /A + "' + @formatfilename + '_" /A "' + @formatfilename + '" /A'
exec master.dbo.xp_cmdshell @xpcmd, no_output
-- copy out the table using the generated format file
set @bcp = 'bcp "' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '" out "' + @filename + '" -q -T -f "' + @formatfilename + '"'
--print @bcp
exec master.dbo.xp_cmdshell @bcp, no_output
-- delete the temporary file
set @xpcmd = 'DEL "' + @formatfilename + '_"'
exec master.dbo.xp_cmdshell @xpcmd, no_output
next_tbl:
fetch tbl into @TABLE_CATALOG,@TABLE_SCHEMA,@TABLE_NAME
end
close tbl
deallocate tbl
April 20, 2005 at 6:24 am
What is the syntax for calling this stored procedure inside the other script?
format or -f "usp_getformatfile db_name(),user_name(uid),name"?
April 20, 2005 at 6:46 am
This is a new script. The first part ( up to the first go ) creates the stored procedure. This should only be executed once. The part after the go is the new script ( it uses the usp_getformatfile ).
Bert
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply