CODEWITHSUNDEEP
sqlsql-serverdatabase
Cal Brown
Cal Brown

Reputation: 137

SQL table - copy from table to table

I have two tables that are identical to each other on the same database, one is a temporary 'holding' table for uploaded data, the other is the live data table which will store the permanent data.

The reason for this is that the two tables are compared against each other to see changing values from what was there originally and what is newly being updated.

My question is, once the comparison stage has been completed and the user submits the upload, how do I copy over the data from the temporary table to the existing table where it will insert a new record where it does not exist in the existing table and if it does already exist on the permanent table (using a MonthID and yearID), then it should just UPDATE, so that there aren't any duplicates.

So I want it to check if the record exists by seeing if the YearID and MonthID are already there in the other table. Keep in mind that both tables are identical.

Here is some sample data:

Table 1

SpendID Spend    MonthNo YearID  ChangedBy
47      1444.19     2       4       12

Table 2 

SpendID Spend    MonthNo YearID  ChangedBy      
1       1500.54     2       4       12

Sorry, hope this makes sense.

I've tried looking at UPDATE and MERGE, but they seem to work of primary identification keys which I can't use here.

Just need an outside opinion to help with this as I've been struggling.

Upvotes: 0

Views: 81

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You can do this either with MERGE or a INSERT + UPDATE combination. I prefer the latter.

-- Add missing records
INSERT INTO [Table 1] (SpendID, Spend, MonthNo, YearID, ChangedBy)
SELECT
    SpendID
    , Spend
    , MonthNo
    , YearID
    , ChangedBy
FROM [Table 2] t2
WHERE NOT EXISTS (SELECT 1 
                  FROM [Table 1] t1 
                  WHERE t1.MonthNo = t2.MonthNo 
                      AND t1.YearID = t2.YearID)

-- Update existing records
UPDATE t1
    SET t1.Spend = t2.Spend
FROM [Table 1] t1
    INNER JOIN [Table 2] t2 
        ON t1.MonthNo = t2.MonthNo
            AND t1.YearID = t2.YearID

The reason I'd prefer this over MERGE is because MERGE can cause performance problems in certain scenarios. There are a lot of articles online, but I'll just post a link to an article that exposes one problem with MERGE and also relates a few more Connect items, at the bottom of the article, that report issues with MERGE.

Upvotes: 0

Akhatran
Akhatran

Reputation: 19

You can use the following commands to SQLITE: delete-delete, insert, select!!!

Upvotes: 2

Related Questions