How to do an update on existing records?

I have one table of new records (tableA) that may already exist in
tableB. I want to insert these records into tableB with insert if they
don't already exist, or update any existing ones with new data if they
do already exist. A column (Action) in tableA already tells me whether
this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
an insert with

select * into tableB from tableA where Action = 'INSERT'

...and I think I can handle the delete.

But I'm stuck on the update. How do I do the update? An ordinary
UPDATE statement just won't do unless I use a cursor to cycle through
the recordset. I want to avoid a cursor."Google Mike" <googlemike@.hotpop.com> wrote in message
news:25d8d6a8.0402231212.16ab7593@.posting.google.c om...
> I have one table of new records (tableA) that may already exist in
> tableB. I want to insert these records into tableB with insert if they
> don't already exist, or update any existing ones with new data if they
> do already exist. A column (Action) in tableA already tells me whether
> this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
> an insert with
> select * into tableB from tableA where Action = 'INSERT'
> ...and I think I can handle the delete.
> But I'm stuck on the update. How do I do the update? An ordinary
> UPDATE statement just won't do unless I use a cursor to cycle through
> the recordset. I want to avoid a cursor.

I don't completely understand your description, and it would be useful to
see the structure of your tables (ie CREATE TABLE statements), as well as
some sample data. However, here is a fairly generic solution - if it doesn't
work as you expect, then please consider posting the additional information.

/* INSERT new records */

insert into dbo.tableB (col1, col2, ...)
select col1, col2, ...
from dbo.tableA a
where not exists
(select * from dbo.tableB b
where a.PrimaryKeyCol = b.PrimaryKeyCol)
and a.[Action] = 'INSERT'

/* UPDATE existing records */

update dbo.tableB
set col1 = a.col1, col2 = a.col2, ...
from dbo.tableB b
join dbo.tableA a
on a.PrimaryKeyCol = b.PrimaryKeyCol
where a.[Action] = 'UPDATE'

/* DELETE existing records */

delete from dbo.tableB
where exists
(select * from dbo.tableA a
where a.PrimaryKeyCol = dbo.tableB.PrimaryKeyCol
and a.[Action] = 'DELETE')

Note that 'Action' is listed in "Reserved Keywords" as a word to avoid using
in code (at least in SQL 2000 Books Online - you didn't mention which
version of MSSQL you're using).

Simon