best way to transfer data

[earlrainer]

Hi,

I have two databases ,both with different table structure.
I have to move data from about 5 tables from database A to database B.
along the way ,I have to extract data from database A, put it in correct format for database B and then put the data into database B.
what will the fastest method to do that..batch updates,text files etc.
please give your ideas and suggestions.

 

[qyllr]

hello,

i have done a similar task before, and i used bcp and perl... this procedure is assuming that u are bcp'ing out from database A and bcp'ing to database B tables... which means we are truncating database B tables...

1. bcp out the data from database A table 1...
2. run perl script to correct the format and generate the new bcp file...
3. bcp in to database B table 1...

here's the perl script i wrote to correct the format...

# File: fixData.pl
#
# Purpose: This script converts the ASCII "|" delimited file to exclude
# the columns not supported by the XXXX database YYYYY table.
#
# Author(s): mjv
#
# Date: 06-07-01
########################################################################
# OPEN THE FILES NEEDED.
########################################################################

$in_file = $ARGV[0];
$fix_file = $ARGV[1];

open INFILE,"$in_file" or die "Can not open $in_file!";
open OUTFILE,">$fix_file" or die "Can not open $fix_file!";

########################################################################
# STORE THE CONTENT OF THE FILE INTO AN ARRAY
########################################################################

while ($CurRec = <INFILE>)
{
$CurRec =~ s/^\s+//g;
$CurRec =~ s/\s+$//g;

@line = split(/\|/,$CurRec);
$TABLE_ID = $line[0];
########################################################################
# REMOVE COLUMN_1 AND COLUMN_2 FROM THE CURRENT POSITION.
########################################################################
#$COLUMN_1 = $line[1];
#$COLUMN_2 = $line[2];
$COLUMN_3 = $line[3];
$COLUMN_4 = $line[4];
$COLUMN_5 = $line[5];

print OUTFILE (&quot;$TABLE_ID&quot;
print OUTFILE (&quot;|&quot;
print OUTFILE (&quot;$COLUMN_3&quot;
print OUTFILE (&quot;|&quot;
print OUTFILE (&quot;$COLUMN_4&quot;
########################################################################
# ADD BACK COLUMN_1 AND COLUMN_2.
########################################################################
print OUTFILE (&quot;$COLUMN_1&quot;
print OUTFILE (&quot;|&quot;
print OUTFILE (&quot;$COLUMN_2&quot;
print OUTFILE (&quot;|&quot;
print OUTFILE (&quot;$COLUMN_5\n&quot;

$Array_Ele++;
}

#######################################################################
# CLOSE THE FILES.
#######################################################################

close OUTFILE;
close INFILE;

to insure fast bcp in... drop all the check, primary and foreign key constraints in the database b on the affected tables...

after bcp'ing in you just need to re-index... and add the constraints...

hth,
q.

 

[JeanNiBee]

Or, instead of using the perl script to convert your data, just write a 'format' file.

It's part of the bcp syntax and you can find info on it in the ASE Utilities Guide from Sybooks online.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects