I have 2 tables within a database, lets call them table1 and table2. They are related and both have a primary key. Table1 is populated by an application which audits the computers on the network at startup, and table2 is there for me to front-end an audit application to.
I want to populate table2 from table1 every 15 minutes with records which are not already in table2. I have written the following SQL query to do this:
insert into table2(MAC_Address)
select table1.MAC_Address from table1
where MAC_Address <> table1.MAC_Address;
MAC_Address is the primary key in table1 and links to MAC_Address in table2
When i run this statement manually it works fine, however I have created a SQL Server Agent Job with a step to run it and it "succeeds" but never updates table2. I have set the owner of the job to the dbo of the specific db, I have the job step type set to T-SQL - however "run-as" is blank, and I have set the DB to the correct DB.
I am far from an expert at this - database design is not my skillset, however any help will be greatly appreciated.