SanPrg
asked on
Merge Statment
Hi
I have merge statement like below, for eq. I have 1000 rows in t1 target , 900 rows in t2 the source and all of them in t1 target, I found DateModified has a new date for 900, so far is perfect.
But OUTPUT $action gives me 1000 for Update, why?, it should be 900 .
My script like this
Merge t1 t
Using (
select
* from t2
) As s
ON s.ID=t.ID
WHEN MATCHED THEN UPDATE
SET
t.col1=s.col1,
t.col2=s.col2,
t.DateModified=GetDate()
WHEN NOT MATCHED BY TARGET THEN
Insert
(
t.col1,
t.col2,
t.DateModified
)
Values (
s.col1,
s.col2,
GetDate()
)
WHEN NOT MATCHED BY Source THEN UPDATE
SET t.IsActive=0
OUTPUT $action into @Actions;
I have merge statement like below, for eq. I have 1000 rows in t1 target , 900 rows in t2 the source and all of them in t1 target, I found DateModified has a new date for 900, so far is perfect.
But OUTPUT $action gives me 1000 for Update, why?, it should be 900 .
My script like this
Merge t1 t
Using (
select
* from t2
) As s
ON s.ID=t.ID
WHEN MATCHED THEN UPDATE
SET
t.col1=s.col1,
t.col2=s.col2,
t.DateModified=GetDate()
WHEN NOT MATCHED BY TARGET THEN
Insert
(
t.col1,
t.col2,
t.DateModified
)
Values (
s.col1,
s.col2,
GetDate()
)
WHEN NOT MATCHED BY Source THEN UPDATE
SET t.IsActive=0
OUTPUT $action into @Actions;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER