Hi there,

I'm having some trouble in finding out if can I use SQL DTS to perform
UPDATE statements onto my tables.
The environment is something like this: I have an Access Database and a
SQL Server database. It has only one table. And they're exactly the same
with the following structure.

-- Resources
---- ID_Resource INT [Primary Key]
---- resDescription VARCHAR (100)

What I want to do, using DTS something like this:
IF Exists (Select ID_Resource from SQL.Resources WHERE ID_Resource =
ACCESS.Resources.ID_Resource)
UPDATE SQL.Resources SET resDescription =
ACCESS.Resources.resDescription WHERE SQL.Resources.ID_Resource =
ACCESS.Resources.ID_Resource
ELSE
INSERT INTO SQL.Resources VALUES (ACCESS.Resource.ID_Resource,
ACCESS.Resources.resDescription)


The previous SQL script means only to explain the logic involved. I want to
use DTS packaging to run the "IF Exists then UPDATE else INSERT " algorithm.

Thanks in advance.

Hi D.J.,
Actually, using exists in your SQL statement is one way you
can do this. So you could execute two SQL statements in one
execute sql task. In the first, insert values in table2 if
they exist in table1 but not table2. In the second, update
those records that exist in both tables with the values from
table1.
It would just be something along the lines of:

insert into table2
(ID_Resource, res_Description)
select table1.ID_Resource, table1.res_Description
from table1
where not exists
(select *
from table2
where table2.ID_resource = table1.id_resource)

update table2
set res_Description = (select table1.res_Description
from table1
where table1.ID_Resource = table2.Id_Resource)
where exists
(select *
from table1
where table2.ID_resource = table1.id_resource)

-Sue

On Mon, 5 Apr 2004 09:47:41 -0300, "D.J."
<danielj (AT) afc (DOT) com.br> wrote:

Hi Sue,

Thanks you very much for replaying.
I have only one doubt: how shall I know if table 1 is from my Access
Database or from my SQL Database? It may occur that both of them have the
same name.
I'm using the DTS Wizard and my questions might look a little silly,
sorry for that.

Thank you

D.J.

"Sue Hoegemeier" <Sue_H (AT) nomail (DOT) please> escreveu na mensagem
news:0uc67091bud8effdcn45i85avkptm13hcl (AT) 4ax (DOT) com...

If the Access database is being used as a linked server, you
need to use a fully qualified name when querying the Access
data source. So selecting from the access table would be
something like:
YourAccessMDB...table1
and the reference to your SQL table would be something like:
YourDatabase.TableOwner.table1
When you use a four part name, SQL Server knows it's a
linked server. If you don't specify the server of the 4 part
name, SQL Server will assume it's local to the SQL Server
instance.

-Sue

On Wed, 7 Apr 2004 08:31:47 -0300, "D.J."
<danielj (AT) afc (DOT) com.br> wrote: