Prev: Transaction coding style
Next: selecting dates in a date range
From: Andy B. on 24 Mar 2010 03:12
I have the following stored procedure. A few questions about the merge
statement:

1. Can you enclose the entire merge statement in a transaction?
2. In case it's needed, can you enclose only certain clauses in seporate
transactions?
3. In case of the "when not matched by target" clause, can I add multiple
statements in the clause? For example, I want to write to a log anything
that failed or succeeded.

[stored procedure]
create procedure [Headlines].[InsertHeadlines]

(@Headlines as HeadlinesTable readonly)

as

merge [Headlines].[Headlines] as target

using @Headlines as source

on(target.HeadlineID=source.HeadlineID)

when not matched by target then

insert (HeadlineTitle, HeadlineDescription, HeadlineContent,
HeadlineStartDate, HeadlineEndDate, HeadlineLastUpdated)

values
(source.HeadlineTitle,source.HeadlineDescription,source.HeadlineContent,source.HeadlineStartDate,source.HeadlineEndDate,
getdate())

output $action, inserted.HeadlineID, inserted.HeadlineTitle;


From: Uri Dimant on 24 Mar 2010 06:05
Andy

We have couple of SPs that have many DML enclosed in BEGIN TRAN... and MERGE
statement among of them

> 3. In case of the "when not matched by target" clause, can I add multiple
> statements in the clause? For example, I want to write to a log anything
> that failed or succeeded.

TRY BEGIN CATCH..


"Andy B." <a_borka(a)sbcglobal.net> wrote in message
news:etbUdFyyKHA.5288(a)TK2MSFTNGP05.phx.gbl...
>I have the following stored procedure. A few questions about the merge
>statement:
>
> 1. Can you enclose the entire merge statement in a transaction?
> 2. In case it's needed, can you enclose only certain clauses in seporate
> transactions?
> 3. In case of the "when not matched by target" clause, can I add multiple
> statements in the clause? For example, I want to write to a log anything
> that failed or succeeded.
>
> [stored procedure]
> create procedure [Headlines].[InsertHeadlines]
>
> (@Headlines as HeadlinesTable readonly)
>
> as
>
> merge [Headlines].[Headlines] as target
>
> using @Headlines as source
>
> on(target.HeadlineID=source.HeadlineID)
>
> when not matched by target then
>
> insert (HeadlineTitle, HeadlineDescription, HeadlineContent,
> HeadlineStartDate, HeadlineEndDate, HeadlineLastUpdated)
>
> values
> (source.HeadlineTitle,source.HeadlineDescription,source.HeadlineContent,source.HeadlineStartDate,source.HeadlineEndDate,
> getdate())
>
> output $action, inserted.HeadlineID, inserted.HeadlineTitle;
>
>


From: Tibor Karaszi on 24 Mar 2010 06:29
1. Yes, it is a DML command, just like INSERT or UPDATE.

2. No. See above.

3. See syntax for MERGE in Books Online. You can have only one WHEN NOT
MATCHED [ BY TARGET ], but you can have several WHEN NOT MATCHED BY SOURCE
clauses.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Andy B." <a_borka(a)sbcglobal.net> wrote in message
news:etbUdFyyKHA.5288(a)TK2MSFTNGP05.phx.gbl...
> I have the following stored procedure. A few questions about the merge
> statement:
>
> 1. Can you enclose the entire merge statement in a transaction?
> 2. In case it's needed, can you enclose only certain clauses in seporate
> transactions?
> 3. In case of the "when not matched by target" clause, can I add multiple
> statements in the clause? For example, I want to write to a log anything
> that failed or succeeded.
>
> [stored procedure]
> create procedure [Headlines].[InsertHeadlines]
>
> (@Headlines as HeadlinesTable readonly)
>
> as
>
> merge [Headlines].[Headlines] as target
>
> using @Headlines as source
>
> on(target.HeadlineID=source.HeadlineID)
>
> when not matched by target then
>
> insert (HeadlineTitle, HeadlineDescription, HeadlineContent,
> HeadlineStartDate, HeadlineEndDate, HeadlineLastUpdated)
>
> values
> (source.HeadlineTitle,source.HeadlineDescription,source.HeadlineContent,source.HeadlineStartDate,source.HeadlineEndDate,
> getdate())
>
> output $action, inserted.HeadlineID, inserted.HeadlineTitle;
>
>
 | 
Pages: 1
Prev: Transaction coding style
Next: selecting dates in a date range