 | |
Determining data to transform from destination
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Determining data to transform from destination -
11-07-2003
, 05:23 AM
#2
| |||
| |||
|
|
Hi, I'm new to DTS so forgive me if this is a bit trivial I want to transform data from one database to another on a regular basis. I have set up a Transform Data Task no problems that will initially copy the data as required. However, when running the package on subsequent occasions, I only want to append new rows in the source to the destination. Whats the best way to achieve this ie how do I determine which rows to select in my source. All ideas appreciated Paul Lucas |
#3
| |||
| |||
|
|
No-one but yourself is going to be able to tell you how to identify new rows in your table. Ideas 1. Do you have an attribute that indicates when the row was added ? 2. Triggers set up for AFTER INSERT and log to another table will provide the new records as well. 3. Are there attributes that are date dependent so you easily identify when they were added. Once you use one of these methods or any other you can think of then you easily use that as a SQL Statement for the source. Have you thought any about UPDATED records ? -- -- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Paul Lucas" <paul.lucas (AT) precision (DOT) co.uk> wrote in message news:uxDG%23FSpDHA.1284 (AT) TK2MSFTNGP09 (DOT) phx.gbl... Hi, I'm new to DTS so forgive me if this is a bit trivial I want to transform data from one database to another on a regular basis. I have set up a Transform Data Task no problems that will initially copy the data as required. However, when running the package on subsequent occasions, I only want to append new rows in the source to the destination. Whats the best way to achieve this ie how do I determine which rows to select in my source. All ideas appreciated Paul Lucas |
#4
| |||
| |||
|
|
OK.... The source and destination tables contain a unique id column which I thought i could use, however i'm not sure how to go about getting access to the id in the destination from my source connection ie. In my transform source I want to use SELECT * FROM [sourceTable] WHERE SourceId > (SELECT MAX(DestinationId) FROM [destinationTable]) The problem being that sourceTable is available in one connection (database) while the destinationTable is in another connection. "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:u56nkhSpDHA.1656 (AT) tk2msftngp13 (DOT) phx.gbl... No-one but yourself is going to be able to tell you how to identify new rows in your table. Ideas 1. Do you have an attribute that indicates when the row was added ? 2. Triggers set up for AFTER INSERT and log to another table will provide the new records as well. 3. Are there attributes that are date dependent so you easily identify when they were added. Once you use one of these methods or any other you can think of then you easily use that as a SQL Statement for the source. Have you thought any about UPDATED records ? -- -- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Paul Lucas" <paul.lucas (AT) precision (DOT) co.uk> wrote in message news:uxDG%23FSpDHA.1284 (AT) TK2MSFTNGP09 (DOT) phx.gbl... Hi, I'm new to DTS so forgive me if this is a bit trivial I want to transform data from one database to another on a regular basis. I have set up a Transform Data Task no problems that will initially copy the data as required. However, when running the package on subsequent occasions, I only want to append new rows in the source to the destination. Whats the best way to achieve this ie how do I determine which rows to select in my source. All ideas appreciated Paul Lucas |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.