I would like to update some data within existing tables. I currently have data that is in tables but i would like to update some of the fields. I run dts and it populates a staging table. From there i would like to update the last table with the update data from the staging table. The existing data that exists in the last table might not have all the information that is held in the staging table. eg der date may have changed but it has not been updated in the last table
the table design and structure are exactly the same with the same column headings. How do i Update a table that only some of the data has changed. eg NSN number has changed

OK there are a few ways to do this

1. After you move the data from Source to Working table destination you
issue a Blanket update to the real table updating it to the values in the
working table where the key values match (Identifying attributes). To
insert new rows you do the rows in working table that are not in Real table
and the deletes are rows in real table not in source working table. This
presumes you bring ALL the source over

Pros - Easy
Cons - You may be bringing over a lot of data and therefore this could be
intensive.

2. Through triggers identify rows thaat have had operations defined on them
and log those rows to a seperate table. Use this sperate table as the
source to move to your staging table.

Pros - lighter possible load
Cons - triggers may not be feasible for you and you need to maintain another
table.

3. Have an attribute on each row that identifies LastModTime. This way you
can through triggers update it when an operation happens
You then use a qualified SELECT as your source where the LastModTime is
within your timeframe

Pros - Lighter possible load
Cons - Triggers - Another row on table which could cause problems if you use
SELECT * in some of your statements

Just a few suggestions for you.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

dts and it populates a staging table. From there i would like to update the
last table with the update data from the staging table. The existing data
that exists in the last table might not have all the information that is
held in the staging table. eg der date may have changed but it has not been
updated in the last table.
NSN number has changed.