Depending upon what you want to do with the rows in table A that aren't in table B then it seems like the MERGE command will do what you want. It is designed to insert/update/delete data from a table given another table's data. In your case I suspect you wouldn't worry about removing data.
Get the latest data in SQL SERVER
--I have two tables table A and Table B AND A SCRIPT THAT RUNS Every night, it deletes the data from Table A and populate it from Table B. I am trying to remove the delete
--and insert only the latest data from Table B to Table A, Please advise on how to filter or only the latest modified data
CREATE TABLE A
(ID INT,
NAME VARCHAR(50),
PRICE MONEY,
LSTUPDT_DATE DATETIME,
LOC VARCHAR(10)
);
CREATE TABLE B
(SALESID INT,
SALES_PERSON VARCHAR(50),
SALE_PRICE MONEY,
SALES_DATE DATETIME,
SALES_LOCATION VARCHAR(10)
);
DELETE FROM A;
INSERT INTO A
SELECT ID = B.SALESID,
NAME = B.SALES_PERSON,
PRICE = B.SALE_PRICE,
LSTUPDT_DATE = B.SALES_DATE,
LOC = B.SALES_LOCATION
FROM B;
2 answers
Sort by: Most helpful
-
-
Joyzhao-MSFT 15,566 Reputation points
2021-10-19T02:45:58.387+00:00 Hi @DataNerd ,
If you need to insert the latest data in table B into table A, you may need to compare the difference between table B and table A, and then insert the data into table A. You could try Not in or Not EXISTS or Except statement.
Best Regards,
Joy
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.