SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I add custome tag(Inserted, Updated, deleted) into column of status based on Insert, Update...


How can I add custome tag(Inserted, Updated, deleted) into column of status based on Insert, Update and Delete operation.

Author
Message
Learner44
Learner44
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 452
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.
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69720 Visits: 40917
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[s] inserted into ConfigSet table in XXXXXX'
when 2 then 'Row[s] deleted from ConfigSet table in XXXXXX'
when 3 then 'Row[s] modified in ConfigSet table in XXXXXX'
else 'This should never happen'
end ;
*/
END --TRIGGER
GO




Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Learner44
Learner44
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 452
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search