I have the following INSERT statement that uses a VIEW (named ActiveAGs), if any record in the AGmembership table is not present in the ActiveAGs VIEW then insert into the AG table. So, the records might not be in the AG table at all OR they might be in the AG table but with a status of INACTIVE which means they have to be entered into the AG table (the default value when inserting a record is ACTIVE).
Any idea how I can do this without having to create a view and then use that view in the INSERT statement, is there a way to do it in just one statement?
Thank you in advance.
INSERT INTO AG([GName],AG)
SELECT DISTINCT AGmembership.[GName], AGmembership.AG
FROM AGmembership LEFT OUTER JOIN
ActiveAGs ON AGmembership.[GName] = ActiveAGs.[GName] AND AGmembership.AG = ActiveAGs.AG
WHERE (ActiveAGs.[GName] IS NULL) AND (ActiveAGs.AG IS NULL)
Here is the ActiveAGs View:
SELECT [GName], AG, Status
WHERE (Status = 'Active')