Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9

    Unanswered: Exporting Sybase DB in SQL syntax

    Hello
    does anybody know if you can export all data from a sybase db into SQL statements ?
    Want to dump a db into a textfile with the data in sql format.

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    write a package that
    select 'insert into ...' + ... or whatsoever you want

    BTW isn't BCP an option?

  3. #3
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9
    I haven't been able to get BCP to export data in actual insert statements, which is what I really need.
    I find it very strange that Sybase does not support ( or allow ) users to export there data in SQL format and I really don't want to have to write a package that does that

  4. #4
    Join Date
    Dec 2004
    Posts
    25
    sybase does support this option.
    use the external command 'ddlgen' to facilitate this.
    http://www.sypron.nl/ddlgen.html
    http://manuals.sybase.com/onlinebook...TextView/13262
    Last edited by xmwolverine; 02-16-05 at 15:11.

  5. #5
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Powerbuilder use to have a Save As SQL option. The assumption here is that the entire table would be retrieved (by SELECT *) into the memoryof a Windows machine.

    I used it in the past, and it worked well for ad-hoc exports of 2000-5000 rows. But there was some trouble when actually executing the resulting script. I had to insert a GO word after every 500 inserts. Otherwise, the ISQL client was attempting to send the entire script as a single batch, and a string buffer was being overrun somewhere.

  6. #6
    Join Date
    Aug 2004
    Posts
    42
    The script below is a Unix script that runs an SQL script that creates a SELECT statement that when run creates INSERT statements for the rows in a table. Got that? If you run in Unix you will still need to change the parts that get the Sybase user name and Sybase password for the isql invocation. There are some other assumptions: like the table is owned by dbo. There may be others. No warranties.




    ################################################## #############
    #
    # Create a select statement that creates an INSERT statement for
    # data from a Sybase table.
    ################################################## ##############
    SERVER=$1
    DATABASE=$2
    TABLE=$3

    if [ $# -lt 3 ]
    then
    echo USAGE: CreateInsert SERVER DATABASE TABLE
    return 1
    fi

    LOG=CreateInsert.$LOGNAME.$SERVER.$DATABASE.$TABLE
    rm -f $LOG

    isql -S $SERVER -U $(getsybuser) -w 2048 <<EOF | tee $LOG
    $(getsybpass)
    /************************************************** ************
    *
    * Create a select statement that creates an INSERT statement for
    * data from a Sybase table.
    *
    ************************************************** ************/
    set nocount on
    go
    use $DATABASE
    go

    declare a cursor for
    select c.name
    ,c.usertype
    ,c.status
    from sysobjects o
    ,syscolumns c
    where o.name = "$TABLE"
    and o.id = c.id
    order by c.colid
    go

    declare @TableName varchar(32)
    declare @ColumnName varchar(32)
    declare @Comma char(1)
    declare @Quote char(1)
    declare @ColumnType int
    declare @ColumnStat int

    select @TableName = "$TABLE"
    select @Comma = ' '
    select @Quote = '"'

    if not exists
    (
    select *
    from sysobjects
    where name = @TableName
    and type = 'U'
    )
    begin
    print "Table %1!.$DATABASE..%2! not found!"
    ,@@servername
    ,@TableName
    goto DONE
    end

    print "set rowcount 10"
    print "go"
    print "select"
    print " 'INSERT %1!..%2! SELECT'", '$DATABASE', @TableName

    open a
    while (1 = 1)
    begin
    fetch a into @ColumnName
    ,@ColumnType
    ,@ColumnStat
    if (@@sqlstatus in (1,2)) break

    if (@ColumnType in (1,2,18,24,25) and @ColumnStat & 8 = 0) -- non-NULL char
    begin
    print " ,'%1!%2!' + t.%3! + '%4!'"
    ,@Comma
    ,@Quote
    ,@ColumnName
    ,@Quote
    end
    else if (@ColumnType in (1,2,18,25,24) and @ColumnStat & 8 = 8 ) -- NULL char
    begin
    print " ,'%1!'"
    ,@Comma
    print " + right ('NULL', 4 * (1 - isnull "
    print " (sign (1 + char_length (t.%1!)), 0)))"
    ,@ColumnName
    print " + right ('%1!' + t.%2! + '%3!'"
    ,@Quote
    ,@ColumnName
    ,@Quote
    print " , 255 * sign (1 + char_length (t.%1!)))"
    ,@ColumnName
    end
    else if (@ColumnType in (5,28,10,27,26,13,7,6)) -- integer
    begin
    print " ,'%1!' + str (t.%2!,18,0)"
    ,@Comma
    ,@ColumnName
    end
    else if (@ColumnType in (8,14,23)) -- float
    begin
    print " ,'%1!' + str (t.%2!,30,16)"
    ,@Comma
    ,@ColumnName
    end
    else if (@ColumnType in (12,15,22) and @ColumnStat & 8 = 0) -- non-NULL datetime
    begin
    print " ,'%1!%2!' + convert(char(26),t.%3!,109) + '%4!'"
    ,@Comma
    ,@Quote
    ,@ColumnName
    ,@Quote
    end
    else if (@ColumnType in (12,15,22) and @ColumnStat & 8 = 8) -- NULL datetime
    begin
    print " ,'%1!'"
    ,@Comma

    print " + right ('NULL', 4 * (1 - isnull"
    print " (sign (datepart (year, t.%1!)), 0)))"
    ,@ColumnName

    print " + right ('%1!' + convert(char(26),t.%2!,109) + '%3!'"
    ,@Quote
    ,@ColumnName
    ,@Quote
    print " ,255 * isnull (sign (datepart (year, t.%1!)), 0))"
    ,@ColumnName
    end
    else
    begin
    print "%1! %2! UNKNOWN"
    ,@ColumnName
    ,@ColumnType
    end

    select @Comma = ','
    end
    close a
    deallocate cursor a

    print " "
    print "from %1! t", @TableName
    print "go"
    DONE:
    go

    EOF

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •