February 16, 2018 at 5:58 am
I have a table Col1 is the key identifier. If a record count has no update but a remove then insert, if a record count has update and remove then insert . if a record count has no update of remove then insert. I have a merge statemtent and would like to include this.
Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
I
February 16, 2018 at 6:10 am
Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
I don't understand what you are asking for. Can you provide a clearer example, please?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 16, 2018 at 6:15 am
jesseflanders - Friday, February 16, 2018 5:58 AMI have a table Col1 is the key identifier. If a record count has no update but a remove then insert, if a record count has update and remove then insert . if a record count has no update of remove then insert. I have a merge statemtent and would like to include this.Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
I don't understand what you are asking for. Can you provide a clearer example, please?
IF COL1 DOES NOT EXIST THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS REMOVE THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS NO REMOVE THEN DO NOT INSERT
IF COL1 EXIST AND COL2 HAS NO UPDATE AND COL2 DOES HAVE REMOVE THEN INSERT
February 16, 2018 at 6:18 am
Phil Parkin - Friday, February 16, 2018 6:10 AMjesseflanders - Friday, February 16, 2018 5:58 AMI have a table Col1 is the key identifier. If a record count has no update but a remove then insert, if a record count has update and remove then insert . if a record count has no update of remove then insert. I have a merge statemtent and would like to include this.Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVEI don't understand what you are asking for. Can you provide a clearer example, please?
IF COL1 DOES NOT EXIST THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS REMOVE THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS NO REMOVE THEN DO NOT INSERT
IF COL1 EXIST AND COL2 HAS NO UPDATE AND COL2 DOES HAVE REMOVE THEN INSERT
INSERT what?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 16, 2018 at 6:22 am
jesseflanders - Friday, February 16, 2018 6:15 AMPhil Parkin - Friday, February 16, 2018 6:10 AMjesseflanders - Friday, February 16, 2018 5:58 AMI have a table Col1 is the key identifier. If a record count has no update but a remove then insert, if a record count has update and remove then insert . if a record count has no update of remove then insert. I have a merge statemtent and would like to include this.Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVEI don't understand what you are asking for. Can you provide a clearer example, please?
IF COL1 DOES NOT EXIST THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS REMOVE THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS NO REMOVE THEN DO NOT INSERT
IF COL1 EXIST AND COL2 HAS NO UPDATE AND COL2 DOES HAVE REMOVE THEN INSERT
INSERT what?
Again I'm using a simple merge and is the key COL1
BEGIN TRANSACTION
MERGE
Table1 AS target
USING
@TmpTable AS source
ON
source.col1 = target.col1
AND target.Status <> 'V'
WHEN MATCHED THEN
UPDATE SET @p_Return_Text = 'Processed'
WHEN NOT MATCHED BY TARGET THEN
INSERT ( col1
col2
col3
col4
)
COL1
February 16, 2018 at 6:39 am
Per this example
Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
Condition 1: If a record count has no update but a remove then insert
Condition 2:if a record count has update and remove then insert
Condition 3:if a record count has no update of remove then insert
888 has both UPDATE and REMOVE therefore the MERGE should Insert the record 888 satisying Condition 2
122 ? what should be done. Infact what does Condition 3 mean "no update of remove"
145 ? same question as i have for 122
126 Satisfies Condition 1 so Insert.
Please clarify
February 16, 2018 at 7:03 am
george-178499 - Friday, February 16, 2018 6:39 AMPer this exampleCol1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
Condition 1: If a record count has no update but a remove then insert
Condition 2:if a record count has update and remove then insert
Condition 3:if a record count has no update of remove then insert888 has both UPDATE and REMOVE therefore the MERGE should Insert the record 888 satisying Condition 2
122 ? what should be done. Infact what does Condition 3 mean "no update of remove"
145 ? same question as i have for 122
126 Satisfies Condition 1 so Insert.Please clarify
Sorry for the confusion I meant no update or Remove. The col1 exist in the table and has one conditon Update. We do nothing.
If Col1 lets say 777 came in and didin't exist at all I would Insert and Col1 777 and insert Col2 Update
February 16, 2018 at 10:25 am
Just to understand better
Could you share the sample rows for source_table and destination_table
I suppose your destination table has col1 as the primary key?
February 16, 2018 at 12:17 pm
jesseflanders - Friday, February 16, 2018 5:58 AMI have a table Col1 is the key identifier. If a record count has no update but a remove then insert, if a record count has update and remove then insert . if a record count has no update of remove then insert. I have a merge statemtent and would like to include this.Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVE
>> have a table Col1 is the key identifier. <<
Please read what you posted. We don't even know the name of this table! Every table must have a key, by definition. This means it has to be unique. But when we look at what you posted your poorly named "col_1" has duplicate values so we can never be a key, by definition. You don't seem to understand that being a key in the table is how an attribute is used, and not what it is by its nature.
>> If a record [sic] count has no 'up' but a remove then insert, if a record [sic] count has 'up' and remove then insert . if a record count has no 'up' of remove then insert. I have a merge statemtent and would like to include this. <<
You have no count in this table; the rows in a table have no ordering, so there is no concept of an insert following an 'update', unless yoyu explieictly provide this infomation.
Rows are nothing whatsoever like records. Your "col_2" contains a verb, not a value. In short, none of this makes any sense. You talk about having a merge statement,so where's the code? But in order to have a merge statement, you need a source table and a target table; we don't have that here. Again, we don't even know the name of the table that list of loose text was supposed to fit into!
Guessing at what you're trying to say, I'm going to's assume that you're trying to set up some kind of semaphore system (I would assume by now you run into Dijkstra and the PV semaphores in your first computer science classes).
CREATE TABLE Semaphores
(semaphone_id CHAR(5) NOT NULL,
event_seq INTEGER NOT NULL
PRIMARY KEY (semaphone_id, event_seq)
flag CHAR(2) NOT NULL
CHECK(flag IN ('up', 'dn'))
INSERT INTO Semaphore
('00888', 1, 'up'),
('00888', 2, 'dn'),
('00122', 1, 'up'
('00145', 1, 'up')
('00126', 1, 'dn');
Using this model, the highest event sequence number will be the current state of your semaphore.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 16, 2018 at 12:53 pm
Sorry,
However the merge cannot used in the way I'm counting Distinct records prior to insert. Very limited
The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first is not. If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Only columns from the target table can be referenced in <clause_search_condition>
I guess a simple lookup on Microsoft site would have helped.
Cheers
February 16, 2018 at 9:26 pm
Phil Parkin - Friday, February 16, 2018 6:10 AMjesseflanders - Friday, February 16, 2018 5:58 AMI have a table Col1 is the key identifier. If a record count has no update but a remove then insert, if a record count has update and remove then insert . if a record count has no update of remove then insert. I have a merge statemtent and would like to include this.Right now my merge is only ON
source.Col1 = target.Col1Col1 Col2
888 UPDATE
888 REMOVE
122 UPDATE
145 UPDATE
126 REMOVEI don't understand what you are asking for. Can you provide a clearer example, please?
IF COL1 DOES NOT EXIST THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS REMOVE THEN INSERT
IF COL1 EXIST AND COL2 HAS UPDATE AND COL2 HAS NO REMOVE THEN DO NOT INSERT
IF COL1 EXIST AND COL2 HAS NO UPDATE AND COL2 DOES HAVE REMOVE THEN INSERT
From what I can understand of all of this, you need to take all of the multiple rows for the same Col1 value, and unpivot them into separate columns. Then, you can have the information needed to perform your work. Going with this, you can only update a row once in the target table of a merge, so the two "888" values would violate this - unless they have to be considered together. So, let's unpivot your data so that everything is on one row per Col1:
WITH cte AS
(
-- See how I made the data so that is easy for others to copy and use???
-- It would help us out if you had done this.
-- We call this "Help us help you"
SELECT *
FROM (VALUES (888, 'UPDATE'),
(888, 'REMOVE'),
(122, 'UPDATE'),
(145, 'UPDATE'),
(126, 'REMOVE')
) dt(Col1, Col2)
), cte2 AS
(
-- Unpivot the data by Col1
SELECT Col1,
MAX(CASE WHEN Col2 = 'UPDATE' THEN 1 ELSE 0 END) AS Is_Update,
MAX(CASE WHEN Col2 = 'REMOVE' THEN 1 ELSE 0 END) AS Is_Remove,
MAX(CASE WHEN Col2 = 'NO UPDATE' THEN 1 ELSE 0 END) AS Is_NoUpdate,
MAX(CASE WHEN Col2 = 'NO REMOVE' THEN 1 ELSE 0 END) AS Is_NoRemove
FROM cte
GROUP BY Col1
)
-- now calculate the various conditions to determine whether to perform an insert
SELECT *,
CASE WHEN Is_Update = 1 AND Is_Remove = 1 THEN 1
WHEN Is_NoUpdate = 1 AND Is_Remove = 1 THEN 1
WHEN Is_Update = 1 AND Is_NoRemove = 1 THEN 0
ELSE 0
END AS DoInsert
FROM cte2;
This returns :Col1 Is_Update Is_Remove Is_NoUpdate Is_NoRemove DoInsert
----------- ----------- ----------- ----------- ----------- -----------
122 1 0 0 0 0
126 0 1 0 0 0
145 1 0 0 0 0
888 1 1 0 0 1
Now, this can be used to join against a target table in the MERGE statement - just need to check the Do_Insert column for whether to actually do an insert or to ignore the row. Use WHEN MATCHED AND DoInsert = 1 THEN INSERT and WHEN NOT MATCHED BY TARGET THEN INSERT will handle inserting the rows where Col1 is not yet in the target table.
Is this what you are trying to achieve?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply