August 1, 2003 at 5:29 am
hi all,
i have made a package using DTS and i use it to load data into the databse.....
it works ok ..when i run the package for the first time.what i want is that..
when i run the package again..
i want that it should append data into the existing tables ..
can anyone guide me on how to do this..
i'l be grateful
August 1, 2003 at 6:35 am
There are many ways to skin a cat. Depending on your source you could select only rows that are not already in your target table (assuming there are ways to uniquely identify the rows). Another option is to load your source into a temp table and then select rows that are not already in your target table for the insert.
I'm sure you could find many more ways to do this. Cheers.
Joseph
August 4, 2003 at 1:04 pm
Yeah, using a staging table is the way to go. You can use all the DTS Data Transformation Task "Fast Load" options, and it makes the column mapping super simple.
Once the data is into the database then you want a sproc that will decide whether to Insert or Update the record.
Update t1
Set col1 = t2.col1
From Table1 t1 --Your destination
JOIN Table2 t2 --Your Staging table (source)
on t1.UniqueID = t2.UniqueID --Primary Key, most likely
--Then do your Insert
Insert Table1
Select t2.col1
From Table2 t2
LEFT JOIN Table1 t1 on t2.UniqueID = t1.UniqueID
Where t1.UniqueID is null--Effectively all records not already in Table1
Have fun...DTS is pretty cool.
Signature is NULL
August 5, 2003 at 8:00 am
By Deafult whn you define a Transformation (DTS Data Pump Task)it will append to the table. You do not need to do anything. If you happen to use DTS wizard to create the package then you could actually see the properties of the transformation and choose to append or delete.
August 7, 2003 at 5:03 am
i have the same problem but what i want to do is: i have a sybase database and sql server on different machine, and i want everytime there is changes on sybase, sql server will also updated, and i prefer do something to make it happen in sql server not in sybase, can you help me, asap?
August 11, 2003 at 12:02 pm
novalhendra, if you want actual "Real-Time" updates I have no idea how you would do that across platforms.
The best I could do would be to do updates to the data on a regular schedule. DTS would be the obvious choice for this, but you may need some additional sybase plug-ins.
cl
Signature is NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply