SQL: need to create a sql job

Occasional Contributor

Hi Team, I want to automate one of my SQL project and need help for same. I’m inserting a data in my dimension table on production server from our reference server based on any new row found in reference table.  I’d like implement following things to get it automate ..

 

Query ..

INSERT INTO PROD_DB.[dbo].[DimAccount]

Select * from Ref01.REF_DB.[dbo].[DimAccount] D1 left join PROD_DB.[dbo].[DimAccount] D2 on D1.ID=D2.ID where ID is null

 

Create a job that should run Bi weekly  to run update query by itself and produce such results in pdf or excel.

 

TheTallman_0-1625762971163.png

 

 

1 Reply
Hi,
It will be good to put this query inside stored procedure and schedule a Jobs that will execute stored procedure automatically. Also you can send an output in excel, .txt or hmtl format.
Sharing two article that you can refer: first one how to create a jobs in SQL Server, and second is how to send query result in email body in .txt file. It can be useful your requirement.
https://sqlskull.com/2019/12/14/schedule-a-job-in-sql/
https://sqlskull.com/2020/03/07/how-to-send-a-sql-server-query-results-in-e-mail-message/