June 4, 2013 at 9:53 am
Hi ,
when I run this , I want to add one tag out of three based on the operation it does on each record wither Inserted or deleted or updated!
how can I achive this.?
thanks.
June 4, 2013 at 10:13 am
maybe soemthing like this might help?
you just need to test for the existence of any data int he INSERTED or DELETED virtual tables
CREATE TRIGGER TR_WHATEVER_NOTIFICATIONS
ON WHATEVER FOR INSERT,UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON
declare @optype tinyint = 0;
if exists (select * from INSERTED) set @optype = @optype + 1
if exists (select * from DELETED) set @optype = @optype + 2
--use soemthing like this?
/*
case @optype
when 1 then 'New row inserted into ConfigSet table in XXXXXX'
when 2 then 'Row deleted from ConfigSet table in XXXXXX'
when 3 then 'Row modified in ConfigSet table in XXXXXX'
else 'This should never happen'
end ;
*/
END --TRIGGER
GO
Lowell
June 4, 2013 at 10:22 am
Hi,
my code is as follows..it is also available on http://www.sqlservercentral.com/articles/EDW/77100/
CREATE PROCEDURE [student].[generate_merge]
@SrcDB SYSNAME, --Name of the Source database
@SrcSchema SYSNAME, --Name of the Source schema
@SrcTable SYSNAME, --Name of the Source table
@TgtDB SYSNAME, --Name of the Target database
@TgtSchema SYSNAME, --Name of the Target schema
@TgtTable SYSNAME, --Name of the Target table
@predicate SYSNAME = null
AS
BEGIN
DECLARE @merge_sql NVARCHAR(MAX); --overall dynamic sql statement for the merge
DECLARE @columns_sql NVARCHAR(MAX); --the dynamic sql to generate the list of columns used in the update, insert, and insert-values portion of the merge dynamic sql
DECLARE @pred_sql NVARCHAR(MAX);--the dynamic sql to generate the predicate/matching-statement of the merge dynamic sql (populates @pred)
DECLARE @updt NVARCHAR(MAX); --contains the comma-seperated columns used in the UPDATE portion of the merge dynamic sql (populated by @columns_sql)
DECLARE @insert NVARCHAR(MAX); --contains the comma-seperated columns used in the INSERT portion of the merge dynamic sql (populated by @insert_sql)
DECLARE @vals NVARCHAR(MAX); --contains the comma-seperated columns used in the VALUES portion of the merge dynamic sql (populated by @vals_sql)
DECLARE @pred NVARCHAR(MAX); --contains the predicate/matching-statement of the merge dynamic sql (populated by @pred_sql)
DECLARE @pred_param NVARCHAR(MAX) = @predicate;
DECLARE @pred_item NVARCHAR(MAX);
DECLARE @done_ind SMALLINT = 0;
DECLARE @dsql_param NVARCHAR(500); --contains the necessary parameters for the dynamic sql execution
--Create the temporary table to collect all the columns shared
--between both the Source and Target tables.
DECLARE @columns TABLE (
table_catalog VARCHAR(100) NULL,
table_schema VARCHAR(100) NULL,
table_name VARCHAR(100) NULL,
column_name VARCHAR(100) NULL,
data_type VARCHAR(100) NULL,
character_maximum_length INT NULL,
numeric_precision INT NULL,
src_column_path VARCHAR(100) NULL,
tgt_column_path VARCHAR(100) NULL
)
--Generate the dynamic sql (@columns_sql) statement that will
--populate the @columns temp table with the columns that will be used in the merge dynamic sql
--The @columns table will contain columns that exist in both the source and target
--tables that have the same data types.
set @columns_sql =
'SELECT
tgt.table_catalog,
tgt.table_schema,
tgt.table_name,
tgt.column_name,
tgt.data_type,
tgt.character_maximum_length,
tgt.numeric_precision,
(src.table_catalog+''.''+src.table_schema+''.''+src.table_name+''.''+src.column_name) AS src_column_path,
(tgt.table_catalog+''.''+tgt.table_schema+''.''+tgt.table_name+''.''+tgt.column_name) AS tgt_column_path
FROM
' + @TgtDB + '.information_schema.columns tgt
INNER JOIN ' + @SrcDB + '.information_schema.columns src
ON tgt.column_name = src.column_name
AND tgt.data_type = src.data_type
AND (tgt.character_maximum_length IS NULL OR tgt.character_maximum_length >= src.character_maximum_length)
AND (tgt.numeric_precision IS NULL OR tgt.numeric_precision >= src.numeric_precision)
WHERE
tgt.table_catalog = ''' + @TgtDB + '''
AND tgt.table_schema = ''' + @TgtSchema + '''
AND tgt.table_name = ''' + @TgtTable + '''
AND src.table_catalog = ''' + @SrcDB + '''
AND src.table_schema = ''' + @SrcSchema + '''
AND src.table_name = ''' + @SrcTable + '''
ORDER BY tgt.ordinal_position'
--execute the @columns_sql dynamic sql and populate @columns table with the data
INSERT INTO @columns
exec sp_executesql @columns_sql
/****************************************************************************************
* This generates the matching statement (aka Predicate) statement of the Merge *
* If a predicate is explicitly passed in, use that to generate the matching statement *
* Else execute the @pred_sql statement to decide what to match on and generate the *
* matching statement automatically *
****************************************************************************************/
IF @pred_param is not null
BEGIN
--if the user passed in a predicate that begins with a comma, strip it out
SET @pred_param = case when SUBSTRING(ltrim(@pred_param),1,1) = ',' then SUBSTRING(@pred_param,(charindex(',',@pred_param)+1),LEN(@pred_param)) else @pred_param end
--if the user passed in a predicate that ends with a comma, strip it out
SET @pred_param = case when SUBSTRING(rtrim(@pred_param),LEN(@pred_param),1) = ',' then SUBSTRING(@pred_param,1,LEN(@pred_param)-1) else @pred_param end
-- loop through the comma-seperated predicate that was passed in via the paramater and construct the predicate statement
WHILE (@done_ind = 0)
BEGIN
set @pred_item = case when charindex(',',@pred_param) > 0 then SUBSTRING(@pred_param,1,(charindex(',',@pred_param)-1)) else @pred_param end
set @pred_param = SUBSTRING(@pred_param,(charindex(',',@pred_param)+1),LEN(@pred_param))
set @pred = case when @pred IS NULL then (coalesce(@pred,'') + 'src.[' + @pred_item + '] = ' + 'tgt.[' + @pred_item + ']') else (coalesce(@pred,'') + ' and ' + 'src.[' + @pred_item + '] = ' + 'tgt.[' + @pred_item + ']') end
set @done_ind = case when @pred_param = @pred_item then 1 else 0 end
END
END
ELSE
BEGIN
set @pred_sql = ' SELECT @predsqlout = COALESCE(@predsqlout+'' and '','''')+' +
'(''''+''src.''+column_name+'' = tgt.''+ccu.column_name)' +
' FROM ' +
@TgtDB + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_tgt' +
' INNER JOIN ' + @TgtDB +'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu' +
' ON tc_tgt.CONSTRAINT_NAME = ccu.Constraint_name' +
' AND tc_tgt.table_schema = ccu.table_schema' +
' AND tc_tgt.table_name = ccu.table_name' +
' WHERE' +
' tc_tgt.CONSTRAINT_TYPE = ''Primary Key''' +
' and tc_tgt.table_catalog = ''' + @TgtDB + '''' +
' and tc_tgt.table_name = ''' + @TgtTable + '''' +
' and tc_tgt.table_schema = ''' + @TgtSchema + ''''
set @dsql_param = '@predsqlout nvarchar(max) OUTPUT'
EXEC sp_executesql
@pred_sql,
@dsql_param,
@predsqlout = @pred OUTPUT;
END
/*************************************************************************
* A Merge statement contains 3 seperate lists of column names *
* 1) List of columns used for Update Statement *
* 2) List of columns used for Insert Statement *
* 3) List of columns used for Values portion of the Insert Statement *
**************************************************************************/
--1) List of columns used for Update Statement
--Populate @updt with the list of columns that will be used to construct the Update Statment portion of the Merge
set @updt = CAST((SELECT ',tgt.[' + column_name + '] = src.[' + column_name + ']'
FROM @columns
where column_name != 'meta_orignl_load_dts' --we want to filter out this column because we do not want the
FOR XML PATH('')) --meta_orginl_load_dts of the target table to be overwritten on updates.
AS NVARCHAR(MAX) --we want to preserve the original date/time the row was written out.
)
--2) List of columns used for Insert Statement
--Populate @insert with the list of columns that will be used to construct the Insert Statment portion of the Merge
set @insert = CAST((SELECT ',' + '[' + column_name + ']'
FROM @columns
FOR XML PATH(''))
AS NVARCHAR(MAX)
)
--3) List of columns used for Insert-Values Statement
--Populate @vals with the list of columns that will be used to construct the Insert-Values Statment portion of the Merge
set @vals = CAST((SELECT ',src.' + '[' + column_name + ']'
FROM @columns
FOR XML PATH(''))
AS NVARCHAR(MAX)
)
/*************************************************************************************
* Generate the final Merge statement using *
* -The parameters (@TgtDB, @TgtSchema, @TgtTable, @SrcDB, @SrcSchema, @SrcTable) *
* -The predicate matching statement (@pred) *
* -The update column list (@updt) *
* -The insert column list (@insert) *
* -The insert-value column list (@vals) *
*************************************************************************************/
SET @merge_sql = (' MERGE into ' + @TgtDB + '.' + @TgtSchema + '.' + @TgtTable + ' tgt ' +
' using ' + @SrcDB + '.' + @SrcSchema + '.' + @SrcTable + ' src ' +
' on ' + @pred +
' when matched then update ' +
' set ' + SUBSTRING(@updt, 2, LEN(@updt)) +
' when not matched then insert (' + SUBSTRING(@insert, 2, LEN(@insert)) + ')' +
' values ( ' + SUBSTRING(@vals, 2, LEN(@vals)) + ');'
);
--Execute the final Merge statement to merge the staging table into production
EXEC sp_executesql @merge_sql;
END;
============================================================
This stored procudre performs the Merge between source and destination.
it takes/decides the columns which are need to be updated, deleted or inserted between source and destination dynamically.
My issue is my detination table has one more column as compared to source table.
That column is called status.
In this column I have to add custome tag/data/value like Inserted, Deleted, Updated ...based on the operations(Insert,Update,Delete) performed on that record while Meging two tables(Source and Destination)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply