I have to update "table2" from another "table1" located in another database. The "table1" located in another database is updated daily with transactions from the last day. I just want to update "table2" with the new values added into "table1". The databases are located in the same server.
Rows are not going to be deleted from "table1", It's a historical record of transactions which I want to replay into "table2" because "table2" (in the other database in the same server) will be used for SSAS, for BI.
I think it requires a procedure. This is what I tried:
USE BDID_BI;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE TransactionUpdate
AS
insert [BDID_BI].[dbo].[Transaction] *
SELECT *
FROM [BDID].[dbo].[hl_transaction] as B
WHERE ( SELECT * FROM [BDID_BI].[dbo].[Transaction]
WHERE date < B.date);
GO
It will be a daily procedure, so I don't have to insert the values previously inserted, I have just to update the new values added into "table1".
CREATE TABLE
statement of the table(s) and whether thetable2
is strictly read only, except for this daily procedure that inserts more rows (that's what I understood, correct me if I'm wrong.) More details means better answer (and usually more upvotes.)