| Author |
Use Insert Update Delete in DTS
|
|
| Sudhir Krishnan 2005-09-19, 9:23 am |
| I have an Access table from which I have to get data periodically in 30
minutes everyday to an SQL server table. both have the same data structure. I
want to get all the new records as insert, modified records as update and
deleted records in source table that still exists in target table to be
deleted. I have tried searching on the net. They all explain about using the
insert, update and delete statements in DDQ. I understand that but how do i
change the related constant in the ActiveX script for eg: Main =
DTSTransformstat_Upd
ateQuery for update. I saw somewhere an eg. where i use a
select case according to some column in the table that has to be changed to
insert or update but i dont want to add a new column for this. some solution
that will compare the source table and target table with sql queries using
exists. but i cant identify target and source in a query if i use the
"Execute SQL task". Can someone help.
| |
| Allan Mitchell 2005-09-19, 8:24 pm |
| I personally find the DDQ slow and not as easy to understand as it
should be. How about creating a linked server of the Access Db from
your SQL Server and you can query the Access DB much more easily.
Allan
"Sudhir Krishnan" < SudhirKrishnan@discu
ssions.microsoft.com> wrote in
message news:9C7B80C3-F173-4725-A5FC- 67B2723C6B55@microso
ft.com:
> I have an Access table from which I have to get data periodically in 30
> minutes everyday to an SQL server table. both have the same data
> structure. I
> want to get all the new records as insert, modified records as update
> and
> deleted records in source table that still exists in target table to be
> deleted. I have tried searching on the net. They all explain about using
> the
> insert, update and delete statements in DDQ. I understand that but how
> do i
> change the related constant in the ActiveX script for eg: Main =
> DTSTransformstat_Upd
ateQuery for update. I saw somewhere an eg. where i
> use a
> select case according to some column in the table that has to be changed
> to
> insert or update but i dont want to add a new column for this. some
> solution
> that will compare the source table and target table with sql queries
> using
> exists. but i cant identify target and source in a query if i use the
> "Execute SQL task". Can someone help.
| |
| Sudhir Krishnan 2005-09-20, 7:23 am |
| Thank you very much Allan. I created a linked server and i am now working on
the Delete, Update, and insert. I hope i can get them working and create
"Execute SQL Tasks" and schedule in DTS
"Allan Mitchell" wrote:
> I personally find the DDQ slow and not as easy to understand as it
> should be. How about creating a linked server of the Access Db from
> your SQL Server and you can query the Access DB much more easily.
>
> Allan
>
> "Sudhir Krishnan" < SudhirKrishnan@discu
ssions.microsoft.com> wrote in
> message news:9C7B80C3-F173-4725-A5FC- 67B2723C6B55@microso
ft.com:
>
>
>
| |
|
| What would you suggest if a linked server is not an option?
I have the same problem, I need to decide wheter to insert or update
depending on the existance of the row in the destination table (as opposed to
a field value in the source table).
I have looked everywhere, including SQLDTS.com but can't find a solution.
All DDQ articles I found merely explained the features.
FJL
"Allan Mitchell" a écrit :
> I personally find the DDQ slow and not as easy to understand as it
> should be. How about creating a linked server of the Access Db from
> your SQL Server and you can query the Access DB much more easily.
>
> Allan
>
> "Sudhir Krishnan" < SudhirKrishnan@discu
ssions.microsoft.com> wrote in
> message news:9C7B80C3-F173-4725-A5FC- 67B2723C6B55@microso
ft.com:
>
>
>
| |
| Sudhir Krishnan 2005-09-20, 9:23 am |
| Can you access the source table and Destination table in SQL? This was my
problem. IF you can then you can write SQLs to get the data and manipulate
it. I can give you the SQLs if you want
"fleo" wrote:
[color=darkred]
> What would you suggest if a linked server is not an option?
> I have the same problem, I need to decide wheter to insert or update
> depending on the existance of the row in the destination table (as opposed to
> a field value in the source table).
>
> I have looked everywhere, including SQLDTS.com but can't find a solution.
> All DDQ articles I found merely explained the features.
>
> FJL
>
>
> "Allan Mitchell" a écrit :
>
| |
|
| Source tables are on SQL Server and destination are on DB2 (sometimes the
opposite). We don't want to use linked servers so the tables are not
accessible in the same SQL.
What I do for now is copying all the source data in a temp table on DB2.
Then I have access to both tables. I perform an UPDATE on existing records
followed by an INSERT new records.
The only purpose I found so far for the DDQ is to insert data in DB2 tables
because the standard Transformation task fails...
"Sudhir Krishnan" a écrit :
[color=darkred]
> Can you access the source table and Destination table in SQL? This was my
> problem. IF you can then you can write SQLs to get the data and manipulate
> it. I can give you the SQLs if you want
>
> "fleo" wrote:
>
|
|
|
|