Query assistance or advice

Hi,
I have a table which I copy nightly via DTS. I would like to copy only the
data which was changed instead based on the MODIFIED date. It will have to
insert any new rows created or update any row
which already exists. This is a sample
TABLE1
ID PRODID NAME QTY MODIFIED
1 123 TEST 1 2006-02-09
2 235 TEST2 2 2006-02-09
3 234 TEST3 5 2006-02-09
TABLE2 (MIRROR)
ID PRODID NAME QTY MODIFIED
1 123 TEST 5 2006-02-07
In this case when I run the query it will update TABLE2 by
updating the qty for id 1 to 1
insert id 2 and 3
Any ideas?
ThanksWhy don't you use triggers? There are several good examples in Books Online.
ML
http://milambda.blogspot.com/|||Doesn't DTS have tools for this? How are you using DTS? I think it has
tools to let you do this directly into table2, checking to see if it needs
to be done.
If you pumping the data right into a temporary table and then running a
query? If so then just write a query like :
insert into table2 (columnList)
select (columnList)
from table1changes as table1
where not exists (select 1
from table2
where table1.id = table2.id)
update table2
set table2.(each column) = table1.(each column)
from table2
join table1changes as table1
on table2.id = table1.id --this must be unique or you
will get (predictably) wierd results
and table1.modified <> table2.modified
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A8518925-0EB0-4D7D-8BD6-2C4FFBE686E4@.microsoft.com...
> Hi,
> I have a table which I copy nightly via DTS. I would like to copy only the
> data which was changed instead based on the MODIFIED date. It will have to
> insert any new rows created or update any row
> which already exists. This is a sample
> TABLE1
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 1 2006-02-09
> 2 235 TEST2 2 2006-02-09
> 3 234 TEST3 5 2006-02-09
> TABLE2 (MIRROR)
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 5 2006-02-07
>
> In this case when I run the query it will update TABLE2 by
> updating the qty for id 1 to 1
> insert id 2 and 3
> Any ideas?
> Thanks|||I am importing from a legacy database. I am currently transferring the entir
e
table nightly but it's hugh so we added a modifieddate column to the table s
o
now I want to check for any records added and updated for a specific date
then check my table in sql server, if records does not exists then insert
them if they do exists then update.
Thanks
"Chris" wrote:

> Hi,
> I have a table which I copy nightly via DTS. I would like to copy only the
> data which was changed instead based on the MODIFIED date. It will have to
> insert any new rows created or update any row
> which already exists. This is a sample
> TABLE1
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 1 2006-02-09
> 2 235 TEST2 2 2006-02-09
> 3 234 TEST3 5 2006-02-09
> TABLE2 (MIRROR)
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 5 2006-02-07
>
> In this case when I run the query it will update TABLE2 by
> updating the qty for id 1 to 1
> insert id 2 and 3
> Any ideas?
> Thanks|||For DTS I am using a transform data task form one data source to the other.
What tools are you talking about?
"Louis Davidson" wrote:

> Doesn't DTS have tools for this? How are you using DTS? I think it has
> tools to let you do this directly into table2, checking to see if it needs
> to be done.
> If you pumping the data right into a temporary table and then running a
> query? If so then just write a query like :
> insert into table2 (columnList)
> select (columnList)
> from table1changes as table1
> where not exists (select 1
> from table2
> where table1.id = table2.id)
> update table2
> set table2.(each column) = table1.(each column)
> from table2
> join table1changes as table1
> on table2.id = table1.id --this must be unique or you
> will get (predictably) wierd results
> and table1.modified <> table2.modified
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A8518925-0EB0-4D7D-8BD6-2C4FFBE686E4@.microsoft.com...
>
>|||I don't know. I am a query/design guy. DTS is a tool I have heard about
and read about but never put into practice. I would think that the
transform task might be able to do a query to check for row existance (maybe
someone else will know?)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F2580A5F-1EEB-4120-82C3-0CE0FF6FEA9C@.microsoft.com...
> For DTS I am using a transform data task form one data source to the
> other.
> What tools are you talking about?
> "Louis Davidson" wrote:
>