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.
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()),'')