June 4, 2013 at 1:34 pm
Hi Lowel, SSCChampion, SSCcrazy eights and other friends.
I am still finding problem in regards of my early posts.
My issue is:
When I use the following stored procedure , to merge data between source and destination , I also need to add fix value/string in the one column called STATUS in the destination.
This value/string is "Deleted", I need to add this values when merge query finds that particular record should be deleted via `WHEN NOT MATCHED BY THE SOURCE`` sql command.
I actully don`t want to delete that records but just want to add delete word in that record`s column which is status.
I need your guidance..
thanks.
my procudre is on http://qa.sqlservercentral.com/articles/EDW/77100/
or
CREATE PROCEDURE [TEST_SCHEMA].[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, --Comma-delimited list (between single qutoes '') of items to match when automatic predicate is not possible. [Does not remove leading/trailing spaces since column names can have leading/trailing spaces]
@match_qual SYSNAME = null, --Pass in additional qualifier logic for the 'WHEN MATCHED THEN' portion of the MERGE statment
@not_match_qual SYSNAME = null, --Pass in additional qualifier logic for the 'WHEN NOT MATCHED THEN' portion of the MERGE statment
@debug SMALLINT = null --Pass in 1 to kick out just the MERGE statement text without executing it
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 @pk_sql NVARCHAR(MAX); --the dynamic sql to populate the @pk table variable that holds the primary keys of the target table
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; --populated by @predicate. used in the dynamic generation of the predicate statment of the merge
DECLARE @pred_item NVARCHAR(MAX); --used as a placeholder of each individual item contained within the explicitley passed in predicate
DECLARE @done_ind SMALLINT = 0; --used in the dynamic generation of the predicate statment of the merge
DECLARE @dsql_param NVARCHAR(500); --contains the necessary parameters for the dynamic sql execution
/************************************************************************************************
* 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. *
************************************************************************************************/
--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 with(nolock)
INNER JOIN ' + @SrcDB + '.information_schema.columns src with(nolock)
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
/**************************************************************************************
* Create the temporary table to collect all the primary key columns *
* These primary key columns will be filtered out of the update portion of the merge *
* We do not want to update any portion of clustered index for performance *
**************************************************************************************/
DECLARE @pk TABLE (
column_name VARCHAR(100) NULL
);
set @pk_sql = 'SELECT ' +
'ccu.column_name ' +
'FROM ' +
@TgtDB + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_tgt with(nolock) ' +
'INNER JOIN ' + @TgtDB +'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu with(nolock) ' +
'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 + ''' '
INSERT INTO @pk
exec sp_executesql @pk_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
-- If a comma-separated list of predicate match items were passed in via @predicate
BEGIN
-- These next two SET statements do basic clean-up on the comma-separated list of predicate items (@pred_param)
-- This basic clean-up only removes leading/trailing commas that are passed in.
-- This DOES NOT remove leading/trailing spaces since column names can have leading/trailing spaces (as stupid as that sounds)
-- 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
-- End clean-up of (@pred_param)
-- 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
-- If an explicite list of predicate match items was NOT passed in then automatically construct the predicate
-- match statement based on the primary keys of the Source and Target tables
BEGIN
set @pred_sql = ' SELECT @predsqlout = COALESCE(@predsqlout+'' and '','''')+' +
'(''''+''src.''+column_name+'' = tgt.''+ccu.column_name)' +
' FROM ' +
@TgtDB + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_tgt with(nolock) ' +
' INNER JOIN ' + @TgtDB +'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu with(nolock) ' +
' 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 c
where c.column_name != 'meta_orignl_load_dts' --we do not want the original time the row was created to be overwritten
and not exists (select 'x' from @pk p where p.column_name = c.column_name) --we do not want the primary key columns updated for performace
FOR XML PATH(''))
AS NVARCHAR(MAX)
)
--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 following... *
* -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) *
* -Filter out Primary Key from the update (updating primary key essentially *
* turns the update into an insert and you lose all efficiency benefits) *
*************************************************************************************/
SET @merge_sql = (' MERGE into ' + @TgtDB + '.' + @TgtSchema + '.' + @TgtTable + ' tgt ' +
' using ' + @SrcDB + '.' + @SrcSchema + '.' + @SrcTable + ' src ' +
' on ' + @pred +
' when matched ' + coalesce(@match_qual,'') + ' then update ' +
' set ' + SUBSTRING(@updt, 2, LEN(@updt)) +
' when not matched ' + coalesce(@not_match_qual,'') + ' then insert (' + SUBSTRING(@insert, 2, LEN(@insert)) + ')' +
' values ( ' + SUBSTRING(@vals, 2, LEN(@vals)) + ')' +
' when not matched by source then delete;' --ADDED THIS LINE TO DELTE ROWS OFF OF TARGET TABLE THAT DO NOT EXIST IN SOURCE
);
--Either execute the final Merge statement to merge the staging table into production
--Or kick out the actual merge statement text if debug is turned on (@debug=1)
IF @debug = 1
BEGIN
-- If debug is turned on simply select the text of merge statement and return that
select @merge_sql;
END
ELSE
BEGIN
-- If debug is not turned on then execute the merge statement
EXEC sp_executesql @merge_sql;
END
END;
June 4, 2013 at 1:58 pm
we don't need the source code in this case, we just need to see the exact command you are trying to execute, i think;
exec [generate_merge] 'CDM','dbo','tbstate','CDMLIVE','dbo','tbstate',@debug=1
the issue with the code you are using, is it produces the exact command needed to merge between the two tables, or a tighter list if you are including the values for the parameters @match_qual and @not_match_qual;
run the code with the parameter @DEBUG =1,;
Maybe you can paste it here for some better help.
then you have the command that would be executed, so you can modify it to update specific columns.
you'll never get it to auto-generate it as is, so you'll need to modify the code it produces instead, and run teh modified code.
Lowell
June 4, 2013 at 2:12 pm
Hi ,
This is the command I am using to run the SP.
EXEC@return_value = [dbo].[generate_merge1]
@SrcDB = LINKEDCP,
@SrcSchema = dbo,
@SrcTable = student,
@TgtDB = LINKEDCP,
@TgtSchema = dbo,
@TgtTable = totalstudent,
@predicate = StudentID,
@match_qual = NULL,
@not_match_qual = NULL,
@debug = 1
SELECT'Return Value' = @return_value
basically the stored procedure which i posted above works fine for Incremental ETL(Insert,Update and Delete) but in my case for the record which will be deleted..I actully have to tag them as deleted in status column, but don't have to remove them.
I managed to get it with the following query which i hardcoded for one table.
WHEN NOT MATCHED BY SOURCE THEN update set d.STATUS='DELETED' .
but the thing is the above stored procudre works for any table, its completely dynamic . I want this procudre to do this as I just metioned in above line.
when I tried following:
SET @merge_sql = (' MERGE into ' + @TgtDB + '.' + @TgtSchema + '.' + @TgtTable + ' tgt ' +
' using ' + @SrcDB + '.' + @SrcSchema + '.' + @SrcTable + ' src ' +
' on ' + @pred +
' when matched ' + coalesce(@match_qual,'') + ' then update ' +
' set ' + SUBSTRING(@updt, 2, LEN(@updt)) +
' when not matched ' + coalesce(@not_match_qual,'') + ' then insert (' + SUBSTRING(@insert, 2, LEN(@insert)) + ')' +
' values ( ' + SUBSTRING(@vals, 2, LEN(@vals)) + ')' +
' when not matched by source then update'+' set '+@TgtTable+'.'+@V+'='+@VALUE1+';' --ADDED THIS LINE TO DELTE ROWS OFF OF TARGET TABLE THAT DO NOT EXIST IN SOURCE
);
where @v is column name called status and @value1 is a string whose value is "deleted" word.
I am getting follwing error.
(0 row(s) affected)
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "student.status" could not be bound.
GO
I don;t know now what to do?
June 4, 2013 at 2:16 pm
don't modify the procedure.
paste the results of the procedure for your table here.
we can help you modify the results,a nd you'll execute those modified results instead.
for example, after formatting, my example produced this:
MERGE INTO CDMLIVE.dbo.tbstate tgt
using CDM.dbo.tbstate src
ON src.STATETBLKEY = tgt.STATETBLKEY
WHEN matched THEN
UPDATE SET tgt.[INDEXTBLKEY] = src.[INDEXTBLKEY],
tgt.[STATECODE] = src.[STATECODE],
tgt.[STATENAME] = src.[STATENAME],
tgt.[FIPS] = src.[FIPS]
WHEN NOT matched THEN
INSERT ([STATETBLKEY],
[INDEXTBLKEY],
[STATECODE],
[STATENAME],
[FIPS])
VALUES ( src.[STATETBLKEY],
src.[INDEXTBLKEY],
src.[STATECODE],
src.[STATENAME],
src.[FIPS])
WHEN NOT matched BY source THEN
DELETE;
that's the code that i would modify to use/don't use other columns or static values.
Lowell
June 4, 2013 at 2:33 pm
Pic: 1
Source table
Student IDNameClassGrade Last Updated
1 Pratik12thA2013-06-04
2 Jigar11th B2013-05-04
Student IDNameClassGrade Last UpdatedStatus
1 Pratik12thA2012-12-04
2 Jigar11th B2012-11-04
3 Daljit 12th A2012-09-08
Destination table.
After applying the stored procedure which takes column dynamically..the result should be.
Student IDNameClassGrade Last UpdatedStatus
1 Pratik12thA2013-06-04Updated
2 Jigar11th B2013-05-04Updated
3 Daljit 12th A2012-09-08Deleted
Destination table
June 4, 2013 at 2:43 pm
run this command with the original/unaltered version of generate_merge , and copy and paste the results:
EXEC [dbo].[generate_merge]
@SrcDB = LINKEDCP,
@SrcSchema = dbo,
@SrcTable = student,
@TgtDB = LINKEDCP,
@TgtSchema = dbo,
@TgtTable = totalstudent,
@predicate = StudentID,
@match_qual = NULL,
@not_match_qual = NULL,
@debug = 1
Lowell
June 4, 2013 at 2:46 pm
MERGE into linked.dbo.student tgt using linked.dbo.studentdetails src on src.[StudentID] = tgt.[ StudentID]
when matched then
update set tgt.[StudentID] = src.[StudentID],
tgt.[NAME] = src.[NAME],
tgt.[class] = src.[class],
tgt.[grade] = src.[grade],
tgt.[Last Updated] = src.[ Last Updated],
tgt.[status]=’updated’
when not matched then
insert (StudentID, Name, Class, Grade, Last Updated, status) values (src.[StudentID], src.[NAME], src.[class], src.[grade], src.[ Last Updated],’Inserted’)
when not matched by source then
update set linked.dbo.student .status=deleted;
I can achive this for single table.
But the procudre which i found on this forum , which ask for paramteres when we run it, with that procudre I am unable to do the above operation.
the procudre which I am using is available on http://qa.sqlservercentral.com/articles/EDW/77100/
Please help me.
June 4, 2013 at 2:51 pm
The result is same
Student IDNameClassGrade Last UpdatedStatus
1[p] Pratik12thA2013-06-04Updated
2 Jigar11th B2013-05-04Updated
3 Daljit 12th A2012-09-08Deleted[/p]
@debug option in this stored procudre just excute the merge statment directly, once it is created instread of going from top line i guess.
I think we have to develope some logic in Merge query itself. so that it also remain dyanmic(take any columns or any tables to merge and also add fix word according to operation like Insert, Update, and delete.
thanks.
June 4, 2013 at 2:58 pm
this is syntactically correct, and what i think you were looking for:
now you will NOT use the procedure, but run this command instead.
MERGE INTO linked.dbo.student tgt
USING linked.dbo.studentdetails src
ON src.[StudentID] = tgt.[ StudentID]
WHEN MATCHED
THEN
UPDATE
SET tgt.[StudentID] = src.[StudentID],
tgt.[NAME] = src.[NAME],
tgt.[class] = src.[class],
tgt.[grade] = src.[grade],
tgt.[Last Updated] = src.[ Last Updated],
tgt.[status] = 'updated'
WHEN NOT MATCHED
THEN
INSERT (
[StudentID],
[Name],
[Class],
[Grade],
[ Last Updated],
[status]
)
VALUES (
src.[StudentID],
src.[NAME],
src.[class],
src.[grade],
src.[ Last Updated],
'Inserted'
)
WHEN NOT MATCHED BY source
THEN
UPDATE
SET tgt.STATUS = 'deleted';
Lowell
June 4, 2013 at 3:09 pm
I did the same thing but the issue is this only for student table, if we we have another table then we need to hardcode everything again for that new table like new columns name into insert , update statment in this query.
but here i have more than 100 tables, so I am considering that stored procdure where I need to give only following parameter and it does everything by itself.
@return_value = [dbo].[generate_merge1]
@SrcDB = ,
@SrcSchema = ,
@SrcTable = ,
@TgtDB = ,
@TgtSchema = ,
@TgtTable = ,
@predicate = ,
@match_qual = NULL,
@not_match_qual = NULL,
@debug = NULL
but I need to add the functionlity which you just gave me in that stored procedure.
and i really don't have any idea how can i achive this.?
June 4, 2013 at 3:33 pm
only adding "Delete" word into the status column will also work for me at this stage.
thanks in advance.
pratik.
June 4, 2013 at 5:23 pm
Can use something like number instead of the string..will it make significance in getting solution of my query?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply