Optimal way of writing to a file after DB Query and then using this file as the data file for BCP in

Question

Requirement is to copy a table say account in server A to table account_two in server B. There are many tables like this each having thousands of rows.

I want to try BCP for it. The problem is account_two might have fewer cols than account. I understand in such scenarios I can either use a format file or a temp table.

The issue is I do not own Server A tables. And in case someone changes the order and the no of col , bcp will fail.

In Sybase queryout is not working. The only option left is doing a select A , B from account in server A and then writing this output to a file and using this file as the date file in BCP IN .

However, since it is huge data I am not able to find a convenient way of doing this.

while ( $my row = $isth->fetchrow_arrayref) {
    print FILE JOIN ("\t",@$row),"\n";
}

But using this performance will be hit.

I cannot use dump_results() or dumper. It will be additional task to bring thousands of lines of data into bcp data file format.

if someone can help me in deciding the best approach.

PS: I am new to PERL. Sorry, if there is an obvious answer to this.

Ωmega · Answer 1 · 2012-04-18T18:35:05.207

#!/usr/local/bin/perl

use strict;
use warnings;
use Sybase::BCP;

my $bcp = new Sybase::BCP $user, $passwd;
$bcp->config(INPUT => 'foo.bcp',
             OUTPUT => 'mydb.dbo.bar',
             SEPARATOR => '|');
$bcp->run;

You should record column names as well, so later you can check if order didn't change. There is no bcp option to retrieve column names, so you have to get that information and store it separatelly.

If you need to reorder them, then:

$bcp->config(...
             REORDER => { 1 => 2,
                          3 => 1,
                          2 => 'foobar',
                          12 => 4},
             ...);

Non-Perl solution:

-- Create the headers file
sqlcmd -Q"SET NOCOUNT ON SELECT 'col1','col2'" -Syour_server -dtempdb -E -W -h-1 -s" " >c:\temp\headers.txt

-- Output data
bcp "SELECT i.col1, col2 FROM x" queryout c:\temp\temp.txt -Syour_server -T -c

-- Combine the files using DOS copy command. NB switches: /B - binary; avoids appending invalid EOF character 26 to end of file.
copy c:\temp\headers.txt + c:\temp\temp.txt c:\temp\output.txt /B

Thanks, but my greatest concern is the NUMBER OF columns and orders might change in the destination table without my prior knowledge. How do I cope with that ? — Gfx, Apr 19 '12 at 01:06
@Gfx - Use `sqlcmd -Q"SET NOCOUNT ON SELECT 'col1','col2'" -Syour_server -dtempdb -E -W -h-1 -s" "` to get **current** column name list and use them in next step in `SELECT` to get data in such order. If some columns are added or removed later, then you have to adjust your system based on such findings. — Ωmega, Apr 19 '12 at 01:15

Optimal way of writing to a file after DB Query and then using this file as the data file for BCP in

1 Answers1