question

Belis-5442 avatar image
0 Votes"
Belis-5442 asked Joyzhao-MSFT answered

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;

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered

Hi @Belis-5442 ,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.