sql server dts: I have a single table called customer, which has new records added daily and
also existing records being modified daily.

I'd like to bring the table over once a day after midnight, however I'm
running into a problem since there are two things I'd like to do, insert
records and update records.

Can anyone point me to the proper direction? I'm sure this is a common task
and was wondering what is the most efficient way to do it.

Thanks all!

Ok

the method you describe is fine if the table is small. You would though not
want to bring over the whole table every time if it was 4 million rows.

You can accomplish what you need in a anumber of ways. Here are two


1. Add a trigger to the table and insert into an auditing table whenever
the table is modified. You then move that table to your other SQL Server.
You can also have an attribute on the audit table of

ChangeType

You would populate this with U, D, I

U = Update
I = Insert
D = Delete

You can then use the Data Driven Query task to issue the correct tyoe of
query onto your destination.


2. Add an attribute onto your base table that is "ModifiedDate". You can
then maintain this through a trigger and everytime you want to load your
destination you can select those rows that were "modified" within the
timeframe you require.


HTH

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
[quoted text, click to view]