Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    10

    Unanswered: help extracting data

    I have about 100 tables I need to extract from a ASE 15.0.2 database. I need to pull all rows from, but I'm running into a couple problems.

    I can't use bcp, because about 20 of the tables need to have a query that only pulls data from the previous day. I know I can create a view, then bcp from the view, I don't want to have to create views for several datbases.

    using isql -Uxxxx -Pxxx -i sql.sql -o outputfile does work, but thanks to isql, it junks up the date-time fields, and don't show the seconds. I know I can do a convert on the date-time fields, but I'm trying to be lazy here, and get away with a select * on all these tables, and not have to write specific querys for each table.

    then there's the next issue, which affects both bcp, and isql methods. The guys that are asking for the data do not want to see NULL. they say it freaks out their file processor. I can write a perl script to strip out the NULLs, and could even have that perl fix the dates, but that's a 2nd script, and I'd prefer to keep those out if possible.

    I'm also very limit as to what I can do on this server, as it's a vendor provided database, so installing perl libraries and stuff like that is out.

    I'm open to anything that might help, or ways I haven't thought about doing this.

    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    try bcp --initstring
    Code:
    bcp tempdb..#t1 out /tmp/t1.txt -Uecourt -Pecourtp -Secourts -c --initstring "select * into #t1 from thetable where thedate>=dateadd(dd,datediff(dd,'',getdate())-1,'') and thedate<dateadd(dd,datediff(dd,'',getdate()),'')

  3. #3
    Join Date
    May 2008
    Posts
    10
    sounds like a plan, so long as I don't fill up tempdb
    Thanks

Posting Permissions

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