Sql-server – Add newest values until current date with the year and month separated as 2 INT columns

MySQLsql server

So I have this query to insert data into a table (year and month are when the row was created)

INSERT INTO Table#1 (ID, col2, Year, Month)

SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth

FROM Table#2 AS A

INNER JOIN Table#3 AS B ON B.ID = A.ID

ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC

The thing is that everytime i run that query, it add all the data all over again, and i want only the newest data from the last update.
So the thing i wanted to do is adding:

WHERE A.InvoiceYear >= MAX(table#1.Year)
AND A.InvoiceMonth > MAX(table#1.Month)

The problem with it, first of all, it didnt work (error 1111: Invalid use of group function), and second, the maximum value for month its going to be 12, because i have data from 4 years. Is there a way to add data that the year and month is more recent that the oldest value added?

Thanks for the help, if something wasnt clear please ask me, it's not my native language

Best Answer

You could use WHERE NOT EXISTS to achieve what you need:

  INSERT INTO Table#1 (ID, col2, Year, Month)
  SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
    FROM Table#2 AS A
    JOIN Table#3 AS B ON B.ID = A.ID
   WHERE NOT EXISTS (SELECT * 
                       FROM Table#1 AS C 
                      WHERE C.Year = A.InvoiceYear
                        AND C.Month = A.InvoiceMonth)
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC;

You can find more information here: EXISTS (Transact-SQL)

Related Question