Re: Binary data migration from MSSQL

>> ** bcp: I'd like to use bcp to do a text import, but I can't figure out how
>> to make the binary data work on either end.  It appears to me that Postgres
>> COPY requires literal binary data to be in octal format (e.g. '\\047').
>> bcp outputs the binary data as an non-escaped ASCII string of hex values
>> (e.g. DF9B52A3). I guess I could write a Java program to convert the hex to
>> escaped octal, then run COPY. This would probably be pretty slow (both from
>> a 20-million row performance standpoint and my personal productivity).
>>
>> Is there a better way to do this?

>I'd lean toward bcp as the fastest method, even if you need to do some data
>massaging. In MSSQL 2000 it appears that bcp can bulk copy a query in similar
>fashion to a table. Perhaps you could do the binary-to-octal transformation in
>the export query from MSSQL?
>
>Joe
 
Thanks for the idea, Joe.  I wrote a 'varbinary-to-octal_string' UDF on the MSSQL Server that I could call within a
queryon the bcp command line.  After some tweaking, it worked great and takes about an hour to dump all 20 million rows
totext (which I think is pretty fast!).
 
 
Wes Sheldah suggested modifying the bcp output with a perl script, but I opted not to do it that way.  Although I don't
knowperl, I could have just as easily used Java.  The drawback of doing it this way is that it's harder for others to
followif there is another language involved.  I know everyone who wants to do this will already have a MSSQL server,
andthat's all you need to create the modified file.
 
 
I'd be happy to write up what I did and contribute it to techdocs, if someone would let me know where to send the
document. I looked around on the techdocs site and didn't see any instructions.   I'm still curious why the binary data
won'tmigrate using pgAdminII, but I guess I'll ask that question on another list.
 
 
Roman Fail
Sr. Web Application Developer
POS Portal, Inc.