OK. I do realize this has been covered before for transforming from
table to table. But in my case, I am parsing a text file and importing
into SQL Server. I need to do this load repetitively with updated text
files. Obviously, many rows in the file will NOT be new and just need
to be updated. Is there a simple way to use DTS or DDQ to accomplish
this? Note that I also need to do some transformations of date/time
values in the text file to the proper format to go into datetime fileds
in the DB. Please help! This is a ciritcal problem for me, and while
I know a lot about ORACLE, I am brand new to SQLServer.

Personally I would import the file into a scratch table and
update/insert/delete from there using TSQL. Your other options are all
Row*Row based and on even a medium file you will notice the difference.

allan

"scott (AT) oax (DOT) com" <scott (AT) oax (DOT) com> wrote

OK. not to show my ignorance again, but can this dual task method then
be automated via DTS? In other words, will I be able to generate a
script that would then be able to be re-run each time the text file is
regenerated? If so, how do I go about setting this up in DTS?

Sure you can schedule this to happen by using a combination of SQL
Server Agent (the scheduling mechanism) (or any other scheduling tool
actually) and DTSRUN. You would have DTSRUN fire your package from a
Job at a given time/interval every time period specified.

Allan



"scott (AT) oax (DOT) com" <scott (AT) oax (DOT) com> wrote

OK. So I have setup the transform task to the staging table and added
a new SQL Task upon succesful completion of the import into the stage
table. What is the syntax of the insert/update statement to execute on
completion of the import? I saw an example somewhere out there. Can
you point me to it?

You have not posted any kind of structure so it is impossible to give
you exact statements.

For your UPDATE statements you will need to find the attribute that
identifies the row of data in the staging table. You then join that to
the real table. You check to see if any attribute in which you are
interested has changed and if it has then you update that value.

For the INSERTS. These are where identifiers are in the staging table
and not in the real table

For the DELETEs. This is where the identifier is in the real table and
not the staging table.

These heneral rules should get you going.

Allan

"scott (AT) oax (DOT) com" <scott (AT) oax (DOT) com> wrote

Sorry. I found the code sample I was looking for. Now I just have to
get the d*** transformations for datetime working after my employee
left it broken. *&@#( We are transforming from two separate columns
(One MM/DD/YY and the other HHMM) into a datetime columns in the
staging table.