|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query assistance or adviceI 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 Why 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 -- Show quoteHide quote---------------------------------------------------------------------------- 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" <Ch***@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 For DTS I am using a transform data task form one data source to the other.
What tools are you talking about? Show quoteHide quote "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" <Ch***@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 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?) -- Show quoteHide quote---------------------------------------------------------------------------- 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" <Ch***@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: > >> 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" <Ch***@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 entire
table nightly but it's hugh so we added a modifieddate column to the table so 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 Show quoteHide quote "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  
Other interesting topics
Problem updating two tables in a transaction.
Does dynamic SQL allow table variables? Insert Trigger DBCC SHOWCONTIG question Problem with Cursor and Union in select Optimizing query with UDF and table vars and IN Reducing 5 values to 1 value Moving indexes from a filegroup to another Misunderstood INSERT when using CASE on ORDER BY SELECT problem in stored procedure |
|||||||||||||||||||||||
