Startseite > Allgemein > Output Activity of Post-Scripts in VS2010/VS2012 Database Projects (SSDT)

Output Activity of Post-Scripts in VS2010/VS2012 Database Projects (SSDT)

In General i use T-SQL MERGE Statement to do the inital fill and updates via post-scripts. If you want to write the the rowcounts of MERGE activity to your deploymentlog you can use the following:

DECLARE @mRowCounts as TTRowCounts
;WITH mySource (ID,x,y,z) as (
SELECT 1,2,3,4 UNION
SELECT 4,5,6,7
)
MERGE [dbo].[TargetTable] t
using  mySource as s on t.ID = s.ID
WHEN matched then
update …
WHEN not matched by target then
insert (..)
when not matched by source then
delete
OUTPUT $action into @Mrowcounts; 
 EXEC [admin].[stPrintMergeRowcount] @MergedTableName = [dbo].[TargetTable]‘, @ttMergeActions = @Mrowcounts;

Helper table-type variable:

 1: CREATE TYPE [dbo].[ttMergeActions] AS TABLE(
 2:     [mergeAction] [nvarchar](10) NULL

Helper stored procedure:

 1: CREATE PROCEDURE [admin].[stPrintMergeRowcount]
 2:     @MergedTableName nvarchar(100) = '', @ttMergeActions ttMergeActions readonly, @full int = 0
 3: AS
 4:  declare @insertCount int, @updateCount int, @deleteCount int;
 5:
 6:  SELECT @insertcount=  SUM(CASE WHEN mergeAction = 'INSERT' THEN 1 ELSE 0 END),
 7:        @updatecount = SUM(CASE WHEN mergeAction = 'UPDATE' THEN 1 ELSE 0 END),
 8:        @deletecount  = SUM(CASE WHEN mergeAction = 'DELETE' THEN 1 ELSE 0 END)
 9:        FROM @ttMergeActions
 10:
 11: print    CASE WHEN @MergedTableName <> '' THEN '** Merged data for table ['+@MergedTableName+'] ' ELSE '' END +
 12:          CASE WHEN @insertcount > 0 OR @Full = 1 THEN '@insertcount = ' + cast(@insertcount as varchar) + ' ' ELSE '' END  +
 13:          CASE WHEN @updatecount > 0 OR @Full = 1 THEN '@updatecount = ' + cast(@updatecount as varchar) + ' ' ELSE '' END  +
 14:          CASE WHEN @deletecount > 0 OR @Full = 1 THEN '@deletecount = ' + cast(@deletecount as varchar) + ' ' ELSE '' END
 15:
 16: RETURN 0
 17: GO
Kategorien:Allgemein Schlagwörter: ,
  1. Es gibt noch keine Kommentare.
  1. No trackbacks yet.

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: