Hi ,

I created a package to transfer some lookup tables store in text format
into SQL server. I able to create the package and run it successfully. But if
any updates
or new record inserted to text file I also want to updates to SQL database.
Old records in the text file I do not want to transer to SQL database.
How can I resolve it ? Any other's idea beside delete all the data in SQL and
transfer again ?

Thanks.
--
Travis Tan

Hi Travis,

"Travis" <Travis (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news:9B19119E-5FBD-4213-9841-0DD3B782462A (AT) microsoft (DOT) com...
in such a scenario you normaly import the data into an staging table first.
You do some matching with the existing data where you decide to
insert/update/ignore import data, and save this result as a state for each
row.
Then you run insert and update statements based on the state of the data in
the staging table.

But this only make sense when the data to insert and update is much less
then the existing data and/or the running system shouldn't be affected - as
less as possible.

Helge

Thanks for you help. But is there possible for me to check data in the text
file
before I actually load into SQL database

"Travis" wrote:

Hi Travis,

"Travis" <Travis (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news:0D1D5632-8A3D-4426-9AE1-5253D8521B9B (AT) microsoft (DOT) com...
there is no good way to check it before load, because you would check it in
the file row by row, which woul take much longer then anything you do with
SQLServer/DTS.
And for updates you have to change the way of inserting also. So you can't
do it with a simple transform anyway.

Helge

You could use subqueries and exists or not exists. To import
new records, something like:
select *
from YourTextFile
where not exists
(select *
from YourTable
where YourTable.YourPK = YourTextFile.YourPK)

For updates, something like:
update YourTable
set YourColumn = (select YourColumn
from YourTextFile
where YourTextFile.YourPK = YourTable.YourPK)
where exists
(select *
from YourTextFile
where YourTextFile.YourPK = YourTable.YourPK)

Your can find more information on using Exists and Not
Exists in the books online help topic:
Using EXISTS and NOT EXISTS to Find Intersection and
Difference

-Sue

On Tue, 3 May 2005 03:23:01 -0700, Travis
<Travis (AT) discussions (DOT) microsoft.com> wrote: