Export long binary field to text file?

[former_SQLA_member1694869]

I try to export long binary field to text file and insert into to another database, but the export of the long binary will not be correct. I'm writing this way:

SELECT long_binary_filed from testtable;
output to 'c:\\temp\\Test1.txt' ENCODING 'utf-8';

What I'm wondering is how I can export long binary fields to a text file so I can add it to another database then with an insert?

[MarkCulp · Accepted solution]

I suggest that you use xp_write_file() since it will not try to do any character conversion on the binary blob when it writes it to the file.

[former_SQLA_member1694869 · Accepted solution]

Okay, thanks for the quick reply. If I then export this.

SELECT xp_write_file( 'c:\\temp\\Test1.txt', long_binary_filed ) FROM testtable;

Do I then read it with xp_read_file? How?

[MarkCulp · Accepted solution]

Yes, one way to read the file would be to use xp_read_file.

begin
  declare @blob long binary;
  set @blob = xp_read_file( 'c:\\temp\\Test1.txt' );
  ...
end;

[former_SQLA_member1694869 · Accepted solution]

Then I do an insert (or update) like this?

INSERT INTO testtable ( long_binary_field ) VALUES ( xp_read_file( 'c:\\temp\\Test1.txt' ) );

[MarkCulp · Accepted solution]

Yes, that should work.