August 31, 2011 at 10:51 am
This works okay, but it's not really what I want... :crazy:
Can anyone help me with a script?
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
What I want is a script (if possible) that will:
update table B where A.CUSTOMER_ID = B.CUSTOMER_ID
When that statement = TRUE
INSERT values (col_1, col_2, etc...) from TABLE A into TABLE B where B.CUSTOMER_ID = A.CUSTOMER_ID
When that statement = FALSE
INSERT values into TABLE B as a new row
The FALSE should never happen because the TRIGGER is going to create the customer record (TABLE A) FIRST then create the supporting data (TABLE B). Also, the business logic in the application prevents B from happening before A.
August 31, 2011 at 11:15 am
I think you are pretty much describing exactly the functionality of the MERGE command.
Look it up in Books OnLine, the help there is comprehensive.
August 31, 2011 at 2:00 pm
No, that is not the merge command OP is describing. OP describes some sort of 2-way merge, updating the first table when there is a match, inserting missing records from either into the other. Merge can only modify one table at a time.
That said, OP also says that the last situation, where table B is to be inserted into, should never happen. If that is the case, merge can be used. However, once you start using it after reading the documentation, you may find merge isn't as obvious to use as it may seem at first glance: it doesn't support a "where" clause. It took me quite some time to figure out that this can be avoided by putting an updateable common table expression (cte for short) where the target table is normally put.
For example, in below script I created an instead of trigger that uses a single merge command to implement the same functionality a table normally has, i.e. insert the rows that were inserted, update the rows that were updated and delete the rows that were deleted. This may seem ridiculous, but it is an example. You can do the same thing on a view for example to update multiple tables using a single insert, update or delete statement and then it makes a lot more sense ;).
The thing to note however in the trigger code is that it defines the merge target as a cte, selecting from the table only those rows that are available in pseudo table "Deleted". Then the "Inserted" pseudo table is used as the source for the merge. Now, any rows that were updated will be matched between source and target, newly inserted rows will not be matched by the target and deleted rows will not be matched by the source.
use tempdb;
go
create table dbo.SomeTable (
col1 int not null,
col2 varchar(10) not null,
col3 datetime not null,
primary key (col1)
);
go
create trigger tbiud_SomeTable
on dbo.SomeTable
instead of insert, update, delete
as
begin
set nocount on;
with cteTarget as (
select tbl.col1, tbl.col2, tbl.col3
from dbo.SomeTable tbl
inner join Deleted d on (d.col1 = tbl.col1)
)
merge into cteTarget trg
using Inserted src
on (src.col1 = trg.col1)
when matched then
update
set col2 = src.col2,
col3 = src.col3
when not matched by target then
insert (col1, col2, col3)
values( src.col1, src.col2, src.col3)
when not matched by source then
delete;
end;
go
insert dbo.SomeTable( col1, col2, col3)
select 1, 'test 1', getutcdate()
insert dbo.SomeTable( col1, col2, col3)
select 2, 'test 2', getutcdate()
update st
set
col2 = reverse(col2)
from dbo.SomeTable st
where st.col1 = 2;
delete from dbo.SomeTable
where col1 = 1;
select *
from dbo.SomeTable;
go
drop table dbo.SomeTable;
Hope this helps you in understanding the merge command.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply