Append data to Sql Server 2000 table from DB2 table (linked server)

Hello,
I am trying to append new records from a DB2 database to a Sql Server
2000 database. Basically table A (Sql Server) has been uploaded with a
dump from table B (DB2) and from know on I would like to append any new
records added to table B to table A. I am running a DTS package to do
this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
primary keys.
Any ideas.
Thanks for your helpThe usual approach is to load the data into a staging table, which
matches the target table in layout but is truncated before the load.
Then you can INSERT to the target table from the staging table where
NOT EXISTS the key. You also have the option of doing an UPDATE to
the target table from the staging table, which would be done before
the INSERT.
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 10:57:21 -0800, mrdata1701@.gmail.com wrote:

>Hello,
>I am trying to append new records from a DB2 database to a Sql Server
>2000 database. Basically table A (Sql Server) has been uploaded with a
>dump from table B (DB2) and from know on I would like to append any new
>records added to table B to table A. I am running a DTS package to do
>this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
>NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
>primary keys.
>Any ideas.
>Thanks for your help|||Thanks Roy! The thing is the table has over 380K records so it would
take a while to update and this is something that will be run daily so
it could end up using more resources that I would like.
On Jan 15, 2:16 pm, Roy Harvey <roy_har...@.snet.net> wrote:[vbcol=seagreen]
> The usual approach is to load the data into a staging table, which
> matches the target table in layout but is truncated before the load.
> Then you can INSERT to the target table from the staging table where
> NOT EXISTS the key. You also have the option of doing an UPDATE to
> the target table from the staging table, which would be done before
> the INSERT.
> Roy Harvey
> Beacon Falls, CT
> On 15 Jan 2007 10:57:21 -0800, mrdata1...@.gmail.com wrote:
>
>
>
>
>|||On 15 Jan 2007 11:26:59 -0800, mrdata1701@.gmail.com wrote:

>Thanks Roy! The thing is the table has over 380K records so it would
>take a while to update and this is something that will be run daily so
>it could end up using more resources that I would like.
The ideal would be to have the DB2 data marked in some indicating
which rows had been updated. Lacking that your alternatives are to
use a staging table as already described, or process today's DB2
extract against yesterday's DB2 extract in an old fashioned match-file
program written in whatever language you prefer. That assumes that
the files are already sorted on the key, of course. Personally I
would try the staging table approach first.
Roy Harvey
Beacon Falls, CT