Hi

I have 2 tables: Source Table - IncidentDimNew and Destination Table - IncidentDimNew with identical columns: Id and CreatedDate but the data is different.

I would like to insert distinct data from those 2 columns of 1st table into the same 2 columns into the 2nd table but I onlt want to replace the values in the 2nd table where the Created Date is > 2015-04

I compiled the code in order to get the information I want from 2 tables:

Source Table

SELECT

COUNT(Id),

LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7)

FROM

IncidentDimNew

Destination Table

SELECT

COUNT(Id),

LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7)

FROM

IncidentDim

This is the code I wrote in order to do it

                     

INSERT INTO IncidentDim 

[Id]
      ,[CreatedDate]
     
SELECT
[Id]
,[CreatedDate]

FROM
IncidentDimNew

where left(CONVERT(VARCHAR(10), CreatedDate, 126),7) > '2015-04'


GO

But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.

So  how do I leave the values as is from table IncidentDim and insert new values as it is from IncidentDimNew where the Created Date is > 2015-04?

Try

merge IncidentDim

using (select Id, CreatedDate from IncidentDimNew where CreatedDate >= '20150401') src on IncidentDim.Id = src.Id

when matched then update set CreatedDate = src.CreatedDate

when not matched then insert (Id, CreatedDate)

values (src.Id, src.CreatedDate)

But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.

Hi UIyana,

Your requirement is not clear, could you please elobrate the quoted sentence?

If you're attempt to insert the rows exists in IncidentDimNew but not in IncidentDim, you can reference the below statement.

;WITH Cte AS
(
SELECT [Id],[CreatedDate] FROM IncidentDimNew idn
WHERE [CreatedDate]>'2015-04-30' 
	  AND NOT EXISTS(SELECT * FROM IncidentDim WHERE [id]=idn.[Id] AND [CreatedDate]=idn.[CreatedDate])
)
INSERT INTO IncidentDimNew([Id],[CreatedDate]) SELECT [Id],[CreatedDate] FROM CTE

If you have any question, feel free to let me know.