June 5, 2013 at 8:34 am
Hi frinds,
I tried to search it down..for the solution , but i think now i have to make a change in planning.
How can i add any thing as a indicator to state that , particular record is deleted, with the stored procedure, during Incremental ELT.
I actually don't want to delete it, but just want to make some kind of indication that record is deleted.
for an example as I also posted yesterday.
Source table
Student ID Name Class Grade Last Updated
1 Pratik 12th A 2013-06-04
2 Jigar 11th B 2013-05-04
Destination table.
Student ID Name Class Grade Last Updated Status
1 Pratik 12th A 2012-12-04
2 Jigar 11th B 2012-11-04
3 Daljit 12th A 2012-09-08
Destination table
After applying the stored procedure which takes column dynamically..the result should be.
Student ID Name Class Grade Last Updated Status
1 Pratik 12th A 2013-06-04
2 Jigar 11th B 2013-05-04
3 Daljit 12th A 2012-09-08 Deleted
I am using this stored procedure.
http://www.sqlservercentral.com/articles/EDW/77100/
Please help me or give me some hint about How I can achive this.?
Thanks.
June 5, 2013 at 8:50 am
If you create a MERGE statement that is going to delete a row, it will delete it....
If you don't want to delete the row at all and simply want to have an additional column to mark a row as inactive, then don't do a delete, add an extra column to your table and do an update to mark it as inactive, along the lines of:-
MERGE Table1 t1
USING Table2 t2
ON t1.ID = t2.ID
WHEN NOT MATCHED BY SOURCE
THEN UPDATE SET t1.DeletedRow = 'True';
Of course, it would be much easier if you posted the DDL, some sample data and the actual query that will be making your deletion/update....
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 9:03 am
How many threads are you going to start on the same topic? In all of them you continue to not provide the details needed to help yet people keep providing pretty much the same answer.
http://www.sqlservercentral.com/Forums/Topic1457931-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1457932-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1458422-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1458785-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1459451-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1459846-444-1.aspx
http://www.sqlservercentral.com/Forums/Topic1459923-391-1.aspx
Maybe you should read this article. It might help you see why nobody can provide you the answer you are looking for.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
The reality is that we want to help you but you have to allow us to help you by providing the details.
_______________________________________________________________
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 5, 2013 at 9:21 am
Hi,
But I really don't knwo how should i delete old threads..that's is the reason I also posted one topic how to delete the threads..yeasterday.
Please let me know how I can delete them..because many of those I got the ansswer from wither you, lowel, or SSCcahmpion and other member of this group.
Please help me.
June 5, 2013 at 9:26 am
should I unsubscribe them?
June 5, 2013 at 9:34 am
Ok I guess , I need to jsut replay the post, not actually starting the new topic.!!
Is it right?
June 5, 2013 at 9:53 am
prtk.raval (6/5/2013)
Ok I guess , I need to jsut replay the post, not actually starting the new topic.!!Is it right?
Yes that would be the best approach. The problem with so many threads is that your responses are disconnected. This means that multiple people keep working through how to solve it (which has happened in your case several times now). The worst part of having them all over the place is that you can't figure out which of them has what answers. If it is all in one thread we can all see the other responses and work together to come up with a solution.
The excellent MERGE example in this thread has been posted almost exactly the same way in several of your threads. I am guessing that it doesn't quite do what you want since we keep seeing new posts about it. Give us the details of the tables and data that has been asked for repeatedly. Yes it is some work on your part but you will be rewarded with tested, fast and accurate responses.
_______________________________________________________________
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 5, 2013 at 11:15 am
Please find an attachement , which indicates what I need for the solution and how my solution should look like.
The problem is when I use that merge statment which I found from this address..http://www.sqlservercentral.com/articles/EDW/77100/
It does 99% actually what I want, but for deleting the record from destination table , which are not found in source table, it deletes the records, which is normal .
In my case I want those records to be there but just one more tag indicating that those records are deleted. in one column calles "status".
Please let me know what I shoul provide more.
thanks.
Pratik.
June 5, 2013 at 12:04 pm
Please find an attachement , which indicates what I need for the solution and how my solution should look like.
The problem is when I use that merge statment which I found from this address..http://www.sqlservercentral.com/articles/EDW/77100/
It does 99% actually what I want, but for deleting the record from destination table , which are not found in source table, it deletes the records, which is normal .
In my case I want those records to be there but just one more tag indicating that those records are deleted. in one column calles "status".
Please let me know what I shoul provide more.
thanks.
Pratik.
June 5, 2013 at 12:43 pm
Please friends..give me a hint..so i can start working around..
I ried so many different options but, they are not good for getting solution.
thanks in advance.
pratik.
June 5, 2013 at 12:52 pm
prtk.raval (6/5/2013)
Please find an attachement , which indicates what I need for the solution and how my solution should look like.The problem is when I use that merge statment which I found from this address..http://www.sqlservercentral.com/articles/EDW/77100/
It does 99% actually what I want, but for deleting the record from destination table , which are not found in source table, it deletes the records, which is normal .
In my case I want those records to be there but just one more tag indicating that those records are deleted. in one column calles "status".
Please let me know what I shoul provide more.
thanks.
Pratik.
WHEN NOT MATCHED BY SOURCE
THEN UPDATE SET t1.DeletedRow = 'True';
What about this does not do exactly what you are asking for? This same solution has been posted many times. We have never heard why that doesn't work.
_______________________________________________________________
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 5, 2013 at 2:04 pm
this is working 100% , when we handle only specific table names for source and detination and we can harcode everything.
but the merge statment which I am using , its completely dynamic.
its stored procdure , so when we run it..it ask for SRC database, schema, table and similarly for TRGT.
now the issue is..in this stored procudre merge statment 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)) + ') ;'
and for all this parameters starting with @... there is a logic in that post whic i metioned earlier.
now I have one more column in my destination table which is called status, in that I have to show that this record is "deleted".
so if i right
set @merge_sql1= (' MERGE into ' + @TgtDB + '.' + @TgtSchema + '.' + @TgtTable + ' tgt ' +
' using ' + @SrcDB + '.' + @SrcSchema + '.' + @SrcTable + ' src ' +
' on ' + @pred +' when not matched by target then update set'+ @TgtDB + '.' + @TgtSchema + '.' + @TgtTable+'.status= Deleted ) ;'
it gives me error.
please give me an idea how can i get rid of this.
thanks.
June 5, 2013 at 2:30 pm
prtk.raval (6/5/2013)
this is working 100% , when we handle only specific table names for source and detination and we can harcode everything.but the merge statment which I am using , its completely dynamic.
its stored procdure , so when we run it..it ask for SRC database, schema, table and similarly for TRGT.
now the issue is..in this stored procudre merge statment 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)) + ') ;'
and for all this parameters starting with @... there is a logic in that post whic i metioned earlier.
now I have one more column in my destination table which is called status, in that I have to show that this record is "deleted".
so if i right
set @merge_sql1= (' MERGE into ' + @TgtDB + '.' + @TgtSchema + '.' + @TgtTable + ' tgt ' +
' using ' + @SrcDB + '.' + @SrcSchema + '.' + @SrcTable + ' src ' +
' on ' + @pred +' when not matched by target then update set'+ @TgtDB + '.' + @TgtSchema + '.' + @TgtTable+'.status= Deleted ) ;'
it gives me error.
please give me an idea how can i get rid of this.
thanks.
Boy you really do make this difficult. This is not details, this is code snippet.
it gives me error.
Care to share the error?
Your post is like taking your car to the shop and telling them it is broken, fix it please. The error message is absolutely critical in understanding what the problem is.
I will take a shot in the dark. You are setting the status column to deleted. Since I doubt you have a column named deleted and you are actually wanting to set the value for that column to the string deleted you might want to wrap the word deleted inside of quotes.
status= ''Deleted''
Please note that is NOT a double quote, it is two single quotes because you are already inside a string.
_______________________________________________________________
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 5, 2013 at 2:34 pm
Post the sample DDL statements to create the tables you are using along with some sample data. Then post the actual stored procedure code. As in the actual one you are running. Then let me know what parameters you are using.
Then have a read of the article in Sean's signature, and maybe this one too to get an idea of why you aren't successfully getting the help you need.
I have a feeling Sean's spotted where the problem is, but without seeing the actual code that makes up the stored procedure, it's extremely difficult to help.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 3:39 pm
DESTINATION TABLE
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Student2](
[StudentID] [nchar](2) NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL,
[LASTMODIFIED] [DATE] NOT NULL,
[STATUS] [nchar](10)
) ON [PRIMARY]
GO
SOURCE TABLE
USE [AdventureWorks]
GO
CREATE TABLE [Person].[StudentDetails1](
[StudentID] [nchar](2) NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL,
[LASTMODIFIED] [DATE] NOT NULL,
) ON [PRIMAR
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply