CODEWITHSUNDEEP
sqlsql-server
cnd
cnd

Reputation: 33764

How to update table with only new or changed values?

Here is my query but it makes lots of doubles and I need insert new and (maybe) update changed values.

INSERT INTO [FRIIB].[dbo].[ArchiveAnalog] 
                        (Date, ID, Value)
                        SELECT Date, ID, Value
                            FROM [LinkedOne].[FRIIB].[dbo].[ArchiveAnalog]

My problem is I need to compare not single value , I need to compare whole node with 3 values to check if there is existing ones.

thank you.

Upvotes: 0

Views: 201

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

Assuming SQL Server 2008 or above, use MERGE e.g. 

MERGE INTO [FRIIB].[dbo].[ArchiveAnalog] 
USING (
       SELECT [Date], ID, [Value]
         FROM [LinkedOne].[FRIIB].[dbo].[ArchiveAnalog]
      ) AS source ([Date], ID, [Value])
   ON [Date] = source.[Date]
      AND ID = source.ID
      AND [Value] = source.[Value]
WHEN NOT MATCHED THEN
   INSERT ([Date], ID, [Value])
      VALUES ([Date], ID, [Value]);

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58461

To insert the missing values, following would do

INSERT INTO [FRIIB].[dbo].[ArchiveAnalog] (Date, ID, Value)
SELECT  laa.Date, laa.ID, laa.Value
FROM    [LinkedOne].[FRIIB].[dbo].[ArchiveAnalog] laa
        LEFT OUTER JOIN [FRIIB].[dbo].[ArchiveAnalog] aa ON aa.Date = laa.Date
                                                            AND aa.ID = laa.ID
                                                            AND aa.Value = laa.Value
WHERE   aa.ID IS NULL

To update existing values you might want to look into the MERGE statement

Upvotes: 1

Related Questions