DTS Append Records

I created and saved a DTS package that appends the records from an access
table into a SQL table. Each time I run the DTS package, SQL appends all
records from the access table, leaving duplicate records.
How can I change a DTS package to just import/append only new records from
the access table?I think there are two options here:
1) use 'query' to get your data out rather than using 'copy table'
option. a primary column is handy in this case, e.g. make the access
database as a linked server, then use a join in the query to only get
new records.
2) rebuild the table each time (delete all the records and get the all
data).
Mel|||Can you give me just a sample of code that selects data from a linked server
and inserts it into a sql table?
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145132520.664664.176650@.e56g2000cwe.googlegroups.com...
>I think there are two options here:
> 1) use 'query' to get your data out rather than using 'copy table'
> option. a primary column is handy in this case, e.g. make the access
> database as a linked server, then use a join in the query to only get
> new records.
> 2) rebuild the table each time (delete all the records and get the all
> data).
> Mel
>|||Other options would be to use the ActiveX options in the Transform step
of th data pump. You could compare Primary keys of the rows to be
inserted to the pre-existing rows, and then simply skip that row.
There are a couple of good books on DTS; I recommend Professional SQL
Server 2000 DTS (Amazon link here:
http://www.amazon.com/gp/product/B0...5Fencoding=UTF8
)
HTH,
Stu|||thanks
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1145149418.433259.318180@.g10g2000cwb.googlegroups.com...
> Other options would be to use the ActiveX options in the Transform step
> of th data pump. You could compare Primary keys of the rows to be
> inserted to the pre-existing rows, and then simply skip that row.
> There are a couple of good books on DTS; I recommend Professional SQL
> Server 2000 DTS (Amazon link here:
> http://www.amazon.com/gp/product/B0...5Fencoding=UTF8
> )
> HTH,
> Stu
>|||As requested,
select id, column2, column3
from AccessTable
where id not in
(select id from SQLTable)
id = primary key
The above query will only select (then insert) records that are not yet
in the SQL Table.
Mel|||how can your code insert data without the "INSERT INTO" syntax?
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145218127.199907.322650@.t31g2000cwb.googlegroups.com...
> As requested,
> select id, column2, column3
> from AccessTable
> where id not in
> (select id from SQLTable)
> id = primary key
> The above query will only select (then insert) records that are not yet
> in the SQL Table.
> Mel
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp