"Google Mike" <go********@hotpop.com> wrote in message
news:25**************************@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
