Updating a table from another database

Question

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".

Answer 1

Assuming the databases are on the same server and you are using the default schema, dbo, you can write something similar to this:

INSERT INTO [database2].[dbo].[table2] (<add your columns>)
SELECT (<same columns>)
FROM [database1].[dbo].[table1]

If they're on different servers, you have to deal with adding a linked server to one of the servers and then use four-part naming.

---

Originally a comment by spaghettidba:

Assuming that you have the same columns in the same order on both tables and you always want to insert rows for a newer date, this should do:

INSERT INTO [BDID_BI].[dbo].[Transaction] 
SELECT B.* 
FROM [BDID].[dbo].[hl_transaction] as B
WHERE B.[date] > 
( 
    SELECT MAX(A.[date]) 
    FROM [BDID_BI].[dbo].[Transaction] AS A
);

---

Another comment, this time by Kenneth Fisher:

The above will work but you are making an important assumption, that all of your data was loaded for that date. If you have an identity column in the transaction table I would use that as your test. Something like this:

SELECT B.*
FROM [BDID].[dbo].[hl_transaction] as B 
WHERE B.[ID] > 
( 
    SELECT MAX(A.[ID]) 
    FROM [BDID_BI].[dbo].[Transaction] AS A
);

---

And finally, from SQL Hammer:

Ideally there would be a primary key that exists in both tables so that you could use a LEFT JOIN method for IF NOT EXISTS.

INSERT INTO [BDID_BI].[dbo].[Transaction] 
SELECT B.* 
FROM [BDID].[dbo].[hl_transaction] as B 
LEFT JOIN [BDID_BI].[dbo].[Transaction] AS B_BI 
    ON B.primaryKey = B_BI.primaryKey
WHERE B_BI.primaryKey IS NULL;

Answer 2

Under the assumptions that table2 ([BDID_BI].[dbo].[Transaction]) is not written during the day and only "updated" by this procedure and that table1 (date) doesn't have duplicate values, like this will work:

WITH latest AS
    ( SELECT TOP (1) date
      FROM [BDID_BI].[dbo].[Transaction]
      ORDER BY date DESC
    )
INSERT INTO 
    [BDID_BI].[dbo].[Transaction] 
SELECT 
    B.*
FROM 
    [BDID].[dbo].[hl_transaction] as B
WHERE 
    B.date > (SELECT latest.date FROM latest) ;

If the assumptions do not hold, then you'll need to make adjustments for what happens if table2 has a row inserted with today's datetime (the query will skip copying some of the new rows from table1) and for what happens if table1 has two rows with same date (or datetime, what is the type of the column?) and the first row was already copied in yesterday's execution of the procedure but the second was only added today (the query above will skip copying the second row).

Answer 3

I finally did it, just trying to do it with @spaghettidba query

   INSERT INTO [BDID_BI].[dbo].[Transaction] 
   SELECT B.* 
   FROM [BDID].[dbo].[hl_transaction] as B 
   WHERE B.[date] > ( SELECT MAX(A.[date]) 
                      FROM [BDID_BI].[dbo].[Transaction] AS A)