I want to export the tables with contents from a Sybase database to a text file. I am using Sybase ASE 15.5 and that can't be changed/upgraded.
I have tried using sp_tables and different Select commands, but they don't give the result I'm looking for.
The output format I'm looking for is something like this:
FIRST_TABLE
Column_1 Column_2 Column_3
Roger Male 51
Anne Female 46
SECOND_TABLE
Column_1 Column_2 Column_3
BMW German Car
Schwinn American Bicycles
etc.etc.
Create a view that generates the output you want and use bcp to copy the data from the view.
Consider the following table, view and data:
create table t1 (
k int not null,
v varchar(255) null)
go
create view v1 as
select
'k' as k,
'v' as v
union all
select
convert(varchar, k),
v
from
t1
go
insert into t1 (k, v) values (1, 'Line_1')
insert into t1 (k, v) values (2, 'Line_2')
insert into t1 (k, v) values (3, 'Line_3')
go
Check the data returned from the view, notice the column names are in the result set. They need to here. Ideally you would query against syscolumns, but there is no pivot statement in ASE, so you need to know the names in advance :-(
select * from v1
go
k v
1 Line_1
2 Line_2
3 Line_3
(4 rows affected)
Now copy the data from the view into the text file:
$ bcp <db_name>..v1 out v1.txt -c -U login_name -S server_name
Password:
Starting copy...
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (4000.0 rows per sec.)
$ cat v1.txt
k v
1 Line_1
2 Line_2
3 Line_3
Without using BCP
declare cur cursor for
select sc.name from sysobjects so, syscolumns sc
where so.id = sc.id and so.name = 'FIRST_TABLE'
order by sc.colid
go
declare @l_name varchar(30), @l_sql varchar(2000)
begin
open cur
fetch cur into @l_name
while @@sqlstatus = 0
begin
set @l_sql = @l_sql + '''' + @l_name + ''','
fetch cur into @l_name
end
close cur
deallocate cur
set @l_sql = 'select ' + substring(@l_sql,1,len(@l_sql)-1)
set @l_sql = @l_sql + ' union all select * from FIRST_TABLE'
exec (@l_sql)
end
go
