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 query on the bcp command line. After some tweaking, it worked great and takes about an hour to dump all 20 million rows to text (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 know perl, I could have just as easily used Java. The drawback of doing it this way is that it's harder for others to follow if there is another language involved. I know everyone who wants to do this will already have a MSSQL server, and that'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't migrate using pgAdminII, but I guess I'll ask that question on another list.

Roman Fail
Sr. Web Application Developer
POS Portal, Inc.