如何将 Sybase 表内容导出到文本文件? [英] How to export Sybase table contents to a text file?

问题描述

我想将包含内容的表从 Sybase 数据库导出到文本文件.我使用的是 Sybase ASE 15.5 并且无法更改/升级.

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.

我尝试过使用 sp_tables 和不同的 Select 命令，但它们没有给出我想要的结果.

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

等等等等

推荐答案

创建一个视图来生成您想要的输出并使用 bcp 从视图中复制数据.

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

检查视图返回的数据，注意列名在结果集中.他们需要在这里.理想情况下，您将查询 syscolumns，但 ASE 中没有数据透视语句，因此您需要提前知道名称:-(

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

这篇关于如何将 Sybase 表内容导出到文本文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！