insert delete and insert

Shambhu Rai 1,411

Hi Expert, the below mentioned records having insert first then delete the same record and inserted the same record.

how we can maintain delete and insert records in output. Col2 to Col4 is PK

and wants to merge into this table tabletest20 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME ) expected output:

('D','2','3','4','23','2024-02-14 16:34:40.483')
('I','2','3','4','23','2024-02-14 16:34:41.483')
('D','8','9','10','33',2024-02-14 17:35:39.483') 
('U','12','13','14','16',2024-02-14 18:35:41.483')

below mentioned is query. create table tabletest19 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME )

insert into tabletest19  VALUES('I','2','3','4','23','2024-02-14 16:34:39.483')
insert into tabletest19  VALUES('D','2','3','4','23','2024-02-14 16:34:40.483')
insert into tabletest19  VALUES('I','2','3','4','23','2024-02-14 16:34:41.483')
insert into tabletest19  VALUES('I','8','9','10','33',2024-02-14 17:34:39.483') 
insert into tabletest19  VALUES('D','8','9','10','33',2024-02-14 17:35:39.483') 
insert into tabletest19  VALUES('U','12','13','14','15',2024-02-14 18:34:40.483') 
insert into tabletest19  VALUES('U','12','13','14','16',2024-02-14 18:35:41.483')

Please note it is not duplicate question and every question has different ask

Viorel 118K Reputation points

2024-02-19T16:15:14.86+00:00

Deleted
Shambhu Rai 1,411 Reputation points

2024-02-19T17:07:07.09+00:00
How we can load the record in this table tabletest20 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME )and expected output is this

('D','2','3','4','23','2024-02-14 16:34:40.483') ('I','2','3','4','23','2024-02-14 16:34:41.483') ('D','8','9','10','33',2024-02-14 17:35:39.483') ('U','12','13','14','16',2024-02-14 18:35:41.483')
Shambhu Rai 1,411 Reputation points

2024-02-19T22:02:47.9133333+00:00

Suggestion pls

Erland Sommarskog 112.7K MVP

Please note it is not duplicate question and every question has different ask

Well, at least there is a timestamp to sort the rows.

But we still are in the dark on what your actual aim is. You would probably have had the answer a week ago if you had explained that.

My guess is that you have some sort of delta rows for which you are going to update another table, but you want to delete unnecessary operations. So if there is a delete operation, it's pointless to keep the rows before the delete row. And if there are two updates, only keep the last. If there are two inserts - I guess you want both. Same if you have an Insert followed by an Update.

That leads to:

create table tabletest19 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME )
insert into tabletest19  VALUES('I','2','3','4','23','2024-02-14 16:34:39.483')
insert into tabletest19  VALUES('D','2','3','4','23','2024-02-14 16:34:40.483')
insert into tabletest19  VALUES('I','2','3','4','23','2024-02-14 16:34:41.483')
insert into tabletest19  VALUES('I','8','9','10','33','2024-02-14 17:34:39.483') 
insert into tabletest19  VALUES('D','8','9','10','33','2024-02-14 17:35:39.483') 
insert into tabletest19  VALUES('U','12','13','14','15','2024-02-14 18:34:40.483') 
insert into tabletest19  VALUES('U','12','13','14','16','2024-02-14 18:35:41')
go
DELETE tabletest19
FROM   tabletest19 a
WHERE  EXISTS (SELECT *
               FROM   tabletest19 b
               WHERE  b.col2 = a.col2
                 AND  b.col3 = a.col3
                 AND  b.col4 = a.col4
                 AND  b.col6 > a.col6
                 AND  (b.col1 = 'D' OR b.col1 = 'U' AND a.col1 = 'U'))
go
SELECT * FROM tabletest19
go
DROP TABLE tabletest19

Shambhu Rai 1,411 Reputation points

2024-02-19T23:07:41.7733333+00:00

Please suggest using merge condition while adding another table
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
LiHongMSFT-4306 27,961 Reputation points

2024-02-20T01:30:21.44+00:00

Hi @Shambhu Rai We cannot read your mind, if you want to use merge, why not post that request on your initial post?

How to write a quality question on Microsoft Q&A | Microsoft Learn
Shambhu Rai 1,411 Reputation points

2024-02-20T01:35:38.6333333+00:00

added in main and 2nd comment as well
Shambhu Rai 1,411 Reputation points

2024-02-20T01:40:50.6733333+00:00

my apology added now
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

1 answer

Pinaki Ghatak 4,610 Reputation points Microsoft Employee

2024-02-20T18:23:14.65+00:00
Hello Shambhu Rai

It seems like you’re trying to manage a table tabletest20 with operations such as insert (‘I’), delete (‘D’), and update (‘U’). There is an easier way.

You can use a MERGE statement in SQL to perform these operations in a single query.

MERGE INTO tabletest20 AS target USING (VALUES ('D', '2', '3', '4', '23', '2024-02-14 16:34:40.483'), ('I', '2', '3', '4', '23', '2024-02-14 16:34:41.483'), ('D', '8', '9', '10', '33', '2024-02-14 17:35:39.483'), ('U', '12', '13', '14', '16', '2024-02-14 18:35:41.483') ) AS source (col1, col2, col3, col4, col5, col6) ON (target.col2 = source.col2 AND target.col3 = source.col3 AND target.col4 = source.col4) WHEN MATCHED AND source.col1 = 'D' THEN DELETE WHEN MATCHED AND source.col1 = 'U' THEN UPDATE SET col5 = source.col5, col6 = source.col6 WHEN NOT MATCHED AND source.col1 = 'I' THEN INSERT (col1, col2, col3, col4, col5, col6) VALUES (source.col1, source.col2, source.col3, source.col4, source.col5, source.col6);

This MERGE statement will:

Delete rows from tabletest20 that match on col2, col3, and col4 with a source row where col1 is ‘D’.

Update rows in tabletest20 that match on col2, col3, and col4 with a source row where col1 is ‘U’.

Insert rows into tabletest20 from source rows where col1 is ‘I’ and there is no match on col2, col3, and col4.

Please note that the actual syntax and capabilities of the MERGE statement can vary between different SQL dialects, so you may need to adjust this example to fit your specific database system. Also, please make sure to test this on a small dataset or a backup copy of your data to ensure it behaves as expected. I hope this helps.

If this information provided here helps solve your issue, please tag this as answered, so it helps further community readers, who may have similar questions.
Please sign in to rate this answer.
Erland Sommarskog 112.7K Reputation points MVP

2024-02-20T22:21:47.2833333+00:00

Except that I am not sure that this will work out.

Of course, what is really going to happen with those I, U and D is something that only Shambhu knows, but my reading is that if there is a row with keys (2, 3, 4) we should delete it, and then we should in any case add a new row with the same keys.

If that is the plan, it's not possible to write with a single MERGE. For one thing, MERGE (like all other SQL statements) handles all rows at once, and don't consider any order. If these rows in the expected output are intended to be some sort of instructions, there will have to be a loop.

But of course, I may be all wrong what all this is about. Due to Shambhu's inability to explain what he/she is after, it is very much a guessing game.

Shambhu Rai 1,411 Reputation points

2024-02-20T23:41:05.53+00:00

above ans is incorrect

D- replace previous record with Delete row and output ('D','2','3','4','23','2024-02-14 16:34:40.483') I - multiple insert - max insert record will come ('I','8','9','10','33','2024-02-14 17:34:39.483') same for U and the below D as is as there is not corresponding record to delete ('D','8','9','10','33',2024-02-14 17:35:39.483')
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

insert delete and insert

1 answer

Your answer