Use Insert Update Delete in DTS

#1

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_UpdateQuery 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.

#2

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 (AT) discussions (DOT) microsoft.com> wrote in
message news:9C7B80C3-F173-4725-A5FC-67B2723C6B55 (AT) microsoft (DOT) com:

Quote:

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_UpdateQuery 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.

#3

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:

Quote:

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 (AT) discussions (DOT) microsoft.com> wrote in
message news:9C7B80C3-F173-4725-A5FC-67B2723C6B55 (AT) microsoft (DOT) 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_UpdateQuery 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.

#4

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 :

Quote:

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 (AT) discussions (DOT) microsoft.com> wrote in
message news:9C7B80C3-F173-4725-A5FC-67B2723C6B55 (AT) microsoft (DOT) 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_UpdateQuery 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.

#5

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:

Quote:

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 (AT) discussions (DOT) microsoft.com> wrote in
message news:9C7B80C3-F173-4725-A5FC-67B2723C6B55 (AT) microsoft (DOT) 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_UpdateQuery 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Use Insert Update Delete in DTS

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Use Insert Update Delete in DTS - 09-19-2005 , 08:35 AM

Re: Use Insert Update Delete in DTS - 09-19-2005 , 02:56 PM

Re: Use Insert Update Delete in DTS - 09-20-2005 , 07:04 AM

Re: Use Insert Update Delete in DTS - 09-20-2005 , 08:02 AM

Re: Use Insert Update Delete in DTS - 09-20-2005 , 09:01 AM