question

ASHISHSINGH-6178 avatar image
0 Votes"
ASHISHSINGH-6178 asked CarrinWu-MSFT commented

SQL: need to create a sql job

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.

113065-image.png


sql-server-generalsql-server-reporting-servicessql-server-integration-servicessql-server-analysis-services
image.png (14.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ASHISHSINGH-6178, we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
MartinCairney-6481 avatar image
0 Votes"
MartinCairney-6481 answered

I think what you are looking at here is multiple components or steps - it may not be feasible for a single "Job" depending upon how you expect this job to run.

My suggestion would be to use an SSIS Job - since you can then easily track the counts from each side before the update - do the merge and then generate an output file in CSV format to write in the format you have shown.

You can then use SQL Agent to schedule the SSIS package on whatever schedule you want and even email you the output file if you wish.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered

Hi @ASHISHSINGH-6178,

Welcome to Microsoft Q&A!

Except the methods mentioned by MartinCairney-6481. You also could try to deploy your Power BI reports to SQL Server Reporting Services and try to generate reports. Please refer to Reporting in SQL Server – Power BI Report Server


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.