June 5, 2013 at 3:40 pm
FOR INSERTING DATA INTO DESTINATION TABLE
INSERT INTO [AdventureWorks].[Person].[Student2]VALUES('a7','ms','jessica','ray','a','2008-01-02',null)
GO
INSERT INTO [AdventureWorks].[Person].[Student2]VALUES('a1','mr','paras','ray','s','2011-11-12',null)
GO
INSERT INTO [AdventureWorks].[Person].[Student2]VALUES('a3','ms','koi','ray','a','2009-01-02',null)
GO
INSERT INTO [AdventureWorks].[Person].[Student2]VALUES('a4','ms','moi','ray','a','2013-01-02',null)
GO
INSERT INTO [AdventureWorks].[Person].[Student2]VALUES('a5','mr','jalpa','ray','a','2011-01-02',null)
GO
INSERT INTO [AdventureWorks].[Person].[Student2]VALUES('a6','mr','jones','ray','a','2010-01-02',null)
GO
FOR INSERTING DATA INTO SOURCE TABLE
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a7','ms','jessica','ray','a','2008-01-02')
GO
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a3','m','koi','ray','p','2009-01-02')
GO
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a4','ms','moi','ray','p','2013-01-02')
GO
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a5','mr','jalpa','ray','paras','2011-01-02')
GO
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a6','mr','jones','ray','a','2010-01-02')
GO
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a8','mr','jina','ray','a','2011-11-12')
GO
INSERT INTO [AdventureWorks].[Person].[StudentDetails1]VALUES('a9','mr','Joseph','jal','a','2011-11-11)
GO
June 5, 2013 at 3:41 pm
DESTINATION TABLE SHOULD LOOK LIKE THIS
StudentIDTitleFirstNameMiddleNameLastNameLASTMODIFIEDSTATUS
a1mr pratikroy b2013-06-08NULL
a2ms jessicaray a2012-01-02NULL
a7ms tedoraray a2008-01-02NULL
a3ms koi ray a2009-01-02NULL
a4ms moi ray a2013-01-02NULL
a5mr jalpa ray a2011-01-02NULL
a9 mr Joseph jal a 2011-11-11 NULL
SOURCE TABLE SHOULD LOOK LIKE THIS
StudentIDTitleFirstNameMiddleNameLastNameLASTMODIFIED
a7 msjessicaray a 2008-01-02
a3 mkoi ray p 2009-01-02
a4 msmoi ray p 2013-01-02
a5 mrjalpa ray paras2011-01-02
a6 mrjones ray a 2010-01-02
a8 mrjina ray a 2011-11-12
June 5, 2013 at 3:43 pm
APPLY THIS ATTACHED STORED PROCEDURE..YOU CAN FIND IT AT THE BOTTOM OF THIS ARTICLE'S PAGE http://qa.sqlservercentral.com/Forums/Attachment12862.aspx
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 5, 2013 at 3:46 pm
THE RESULT SHOULD BE LIKE THIS
Student table
StudentIDTitleFirstNameMiddleNameLastNameLASTMODIFIEDSTATUS
a7msjessicaraya2008-01-02NULL
a1mrparasrays2011-11-12NULL
a3mskoiraya2009-01-02NULL
a4msmoiraya2013-01-02NULL
a5mrjalparaya2011-01-02NULL
a6mrjonesraya2010-01-02NULL
a8mrjinaraya2011-11-12NULL
this is the destination table, which is updated by the merge query..
and record no. a9 is deleted..
from here and other record copied from source table.
but i don's want the record no. a9 to be deleted
I want result as follows
StudentIDTitleFirstNameMiddleNameLastNameLASTMODIFIEDSTATUS
a7msjessicaraya2008-01-02NULL
a1mrparasrays2011-11-12NULL
a3mskoiraya2009-01-02NULL
a4msmoiraya2013-01-02NULL
a5mrjalparaya2011-01-02NULL
a6mrjonesraya2010-01-02NULL
a8mrjinaraya2011-11-12NULL
a9msJosephjala2012-11-11DELETED
June 5, 2013 at 3:48 pm
Please help me.
June 5, 2013 at 4:18 pm
This is what I would do:
- Create an audit table to store whatever data you need to save from the file to be deleted.
- Create an "INSTEAD OF DELETE" trigger on your table
- Inside the trigger, fire a procedure to save the data being deleted to the audit file
- Since the trigger will intercept the actual deletion, if you want to actually delete the original row you will have to do it from within the trigger
An example trigger and stored procedure:
CREATE TRIGGER [dbo].[Audit_ExitOnDelete] ON [dbo].[Audit]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @AuditID BIGINT
SELECT @AuditID = AuditID
FROM DELETED
EXEC dbo.Audit_Query '[AuditID]', @AuditID
RETURN
END
CREATE PROCEDURE [dbo].[Audit_Query]
@PKColName NVARCHAR(128)
,@PKValue NVARCHAR(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@AuditTime DATETIME
,@ActionType CHAR(1)
,@strSQL VARCHAR(MAX)
SET @AuditTime = GETDATE()
SET @ActionType = 'Q'
INSERT INTO dbo.AutoAudit
(
[AuditDate]
,[HostName]
,[SysUser]
,[Application]
,[TableName]
,[Operation]
,[SQLStatement]
,[PrimaryKey]
,[RowDescription]
,[SecondaryRow]
,[ColumnName]
,[OldValue]
,[NewValue]
,[RowVersion]
)
VALUES
(
@AuditTime
,HOST_NAME()
,SUSER_SNAME()
,APP_NAME()
,'dbo.Answers'
,@ActionType
,OBJECT_NAME(@@PROCID)
,'[ResultsID]'
,NULL
,NULL
,@PKColName
,@PKValue
,NULL
,1
)
END
June 6, 2013 at 4:05 am
Ok, so looking at what you've provided (and I'm making the assumption that you are only matching on StudentID in this case) If I run the following in debug mode so it only produces the text of the merge statement:-
EXEC Person.GenerateMerge
@SrcDB = 'AdventureWorks'
, @SrcSchema = 'Person'
, @SrcTable = 'StudentDetails1'
, @TgtDB = 'AdventureWorks'
, @TgtSchema = 'Person'
, @TgtTable = 'Student2'
, @predicate = 'StudentID'
, @match_qual = NULL
, @not_match_qual = NULL
, @debug = 1
Then I get the following output:-
MERGE into AdventureWorks.Person.Student2 tgt
using AdventureWorks.Person.StudentDetails1 src
on src.[StudentID] = tgt.[StudentID]
when matched
then update set tgt.[StudentID] = src.[StudentID]
,tgt.[Title] = src.[Title]
,tgt.[FirstName] = src.[FirstName]
,tgt.[MiddleName] = src.[MiddleName]
,tgt.[LastName] = src.[LastName]
,tgt.[LASTMODIFIED] = src.[LASTMODIFIED]
when not matched
then insert ([StudentID],[Title],[FirstName],[MiddleName],[LastName],[LASTMODIFIED])
values ( src.[StudentID],src.[Title],src.[FirstName],src.[MiddleName],src.[LastName],src.[LASTMODIFIED])
when not matched by source then delete;
So, there a couple of things that need to be done. Firstly, the line you've added [Code="sql"]WHEN NOT MATCHED BY SOURCE THEN DELETE;[/code] needs to be changed. What you actually want to see in your MERGE statement is WHEN NOT MATCHED BY SOURCE THEN UPDATE
SET Status = 'DELETED'
However, because the whole procedure is dyanmic, that requires quite a bit more work in the procedure itself to sort out, raising the question at this point - do you really need it to be dynamic?
Also, I think you may have the data in your source and targets set a little off; so I'm using the following instead:-
INSERT INTO Person.Student2
SELECT 'a1', 'mr', 'pratik', 'roy', 'b', '2013-06-08', NULL
UNION ALL
SELECT 'a2', 'ms', 'jessica', 'ray', 'a', '2012-01-02', NULL
UNION ALL
SELECT 'a7', 'ms', 'tedora', 'ray', 'a', '2008-01-02', NULL
UNION ALL
SELECT 'a3', 'ms', 'koi', 'ray', 'a', '2009-01-02', NULL
UNION ALL
SELECT 'a4', 'ms', 'moi', 'ray', 'a', '2013-01-02', NULL
UNION ALL
SELECT 'a5', 'mr', 'jalpa', 'ray', 'a', '2011-01-02', NULL
UNION ALL
SELECT 'a9', 'mr', 'Joseph', 'jal', 'a', '2011-11-11', NULL
INSERT INTO Person.StudentDetails1
SELECT 'a7', 'ms', 'jessica', 'ray', 'a', '2008-01-02'
UNION ALL
SELECT 'a3', 'm', 'koi', 'ray', 'p', '2009-01-02'
UNION ALL
SELECT 'a4', 'ms', 'moi', 'ray', 'p', '2013-01-02'
UNION ALL
SELECT 'a5', 'mr', 'jalpa', 'ray', 'paras', '2011-01-02'
UNION ALL
SELECT 'a6', 'mr', 'jones', 'ray', 'a', '2010-01-02'
UNION ALL
SELECT 'a8', 'mr', 'jina', 'ray', 'a', '2011-11-12'
Bear in mind in your desired results StudentIds a1 and a2 are also going to show as deleted as they are not in the StudentDetails1 table!
Ultimately I think you're after the following statement to be produced:-
MERGE into AdventureWorks.Person.Student2 tgt
using AdventureWorks.Person.StudentDetails1 src
on src.[StudentID] = tgt.[StudentID]
when matched
then update set tgt.[StudentID] = src.[StudentID]
,tgt.[Title] = src.[Title]
,tgt.[FirstName] = src.[FirstName]
,tgt.[MiddleName] = src.[MiddleName]
,tgt.[LastName] = src.[LastName]
,tgt.[LASTMODIFIED] = src.[LASTMODIFIED]
when not MATCHED BY TARGET
then insert ([StudentID],[Title],[FirstName],[MiddleName],[LastName],[LASTMODIFIED])
values ( src.[StudentID],src.[Title],src.[FirstName],src.[MiddleName],src.[LastName],src.[LASTMODIFIED])
when not matched by SOURCE
THEN UPDATE SET STATUS = 'DELETED';
This will return a record set with 9 rows in the Student2 table, with StudentIds a1, a2 and a9 having a status of 'DELETED'.
If you really, really, REALLY need it to be dynamic, I can have a look at that, but it's probably going to be quite complicated. I suspect that the straight up MERGE statement above is what you really need.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 6, 2013 at 9:15 am
when I applied the solution which is provided here, which looks like as follows.
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 STS = DELETED;'
);
I got this error , when I execute procedure.
(32 row(s) affected)
(0 row(s) affected)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DELETED'.
(1 row(s) affected)
what should I do , I also tried this way
In the bigining of the procedure I worte,
declare @keyword sysname = 'deleted'
and in the merge query I made a chage , which you can see in the last row of this query.
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 STS ='+@keyword'+';'
);
still getting same error ,
(32 row(s) affected)
(0 row(s) affected)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DELETED'.
(1 row(s) affected)
What should I do now?
thanks.
June 6, 2013 at 9:38 am
prtk.raval (6/6/2013)
when I applied the solution which is provided here, which looks like as follows.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 STS = DELETED;'
);
I got this error , when I execute procedure.
(32 row(s) affected)
(0 row(s) affected)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DELETED'.
(1 row(s) affected)
what should I do , I also tried this way
In the bigining of the procedure I worte,
declare @keyword sysname = 'deleted'
and in the merge query I made a chage , which you can see in the last row of this query.
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 STS ='+@keyword'+';'
);
still getting same error ,
(32 row(s) affected)
(0 row(s) affected)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DELETED'.
(1 row(s) affected)
What should I do now?
thanks.
The same thing I told you yesterday (http://qa.sqlservercentral.com/Forums/FindPost1460444.aspx). You are trying to update this to a string literal and you do not have the value wrapped with quotes.
Try this:
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 STS = ''DELETED'';'
);
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 9:41 am
Though, personally, I'd really be considering if you need the procedure to be dynamic in that case, as it will now only work for any table that has the column "STS", which seems to defeat the point of it being a dynamic procedure.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 6, 2013 at 9:47 am
[font="Comic Sans MS"]THANK YOU SO SO SO...SO MUCH SSCCRAZYeight, LOWEL, SSC CHAMPION, SSC Journeyman, SSC Veteran AND ALL OTHER MEMEBRS OF THIS FORUM.[/font]
One this I learned most here is
"There is always a perfect answer , but only thing you need to do is ask proper quetion"
thanks you once again.
pratik.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply