update the rows after 1st insert

Question

Hi Expert, there are 2 tables the DATA should insert in 1st row as it has PK(primary key) from col2 to col4 but timestamp is difference so instead of insert it should update the 2 row while inserting into table29.

create table table28  (col1 char(12),col2 char(12),col3 char(12),col4 
create table table29  (col1 char(12),col2 char(12),col3 char(12),col4char(12),DATE_Enter datetime )
insert into table28  VALUES('I','2','3','4','2024-02-14 16:34:39.483')
insert into table28  VALUES('I','2','3','4','2024-02-14 16:34:40.483')
insert into table28  VALUES('U','6','7','8','2024-02-14 16:34:40.483')
insert into table28  VALUES('U','6','7','8','2024-02-14 16:34:41.483')

select * from table28

expected output 
('I','2','3','4','2024-02-14 16:34:40.483')
('U','6','7','8','2024-02-14 16:34:41.483')

Answer 1

Hi @Shambhu Rai

Welcome to the Microsoft Q&A and thank you for posting your questions here.

My understanding of your question and based on the code snippet you provided. The challenges you're facing seems to be that you want to insert data into table29, but if there's already a row with the same primary key values (from col2 to col4), you want to update that row instead of inserting a new one. Also, you want to select specific rows from table28 based on some conditions.

The exact solution to achieve what I described:

-- Create table29 with the same structure as table28 plus an additional column for DATE_Enter
CREATE TABLE table29 (
    col1 CHAR(12),
    col2 CHAR(12),
    col3 CHAR(12),
    col4 CHAR(12),
    DATE_Enter DATETIME
);
-- Insert data into table28
INSERT INTO table28 VALUES('I','2','3','4','2024-02-14 16:34:39.483');
INSERT INTO table28 VALUES('I','2','3','4','2024-02-14 16:34:40.483');
INSERT INTO table28 VALUES('U','6','7','8','2024-02-14 16:34:40.483');
INSERT INTO table28 VALUES('U','6','7','8','2024-02-14 16:34:41.483');
-- Insert data into table29, updating existing rows if necessary
INSERT INTO table29
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY col2, col3, col4 ORDER BY DATE_Enter DESC) AS rn
    FROM table28
) AS t
WHERE t.rn = 1;
-- Select specific rows from table28 based on the conditions provided
SELECT * FROM table28;

1. We create table29 with the same structure as table28 plus an additional column for DATE_Enter.
2. We insert data into table28 as provided in the example.
3. We use a SELECT statement to insert data into table29. Hope this is clearly.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution. Best Regards, Sina