execute multiple scripts and export results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

execute multiple scripts and export results

Author

Topic

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-06 : 20:18:50

Hello,

I have about 20 sql scripts in stored procedures in sql sever 2008. I currently open each SQL script and change file name and type name and then run the execute button to get the result.
I was wondering if there is a way to automate most of my manual job.

Here is my procedure for this job.
1) First, I import raw data (csv) into SQL server using Import and Export Wizard.
2) Open a script in stored procedure (example code below), type my File_Name( the table I created in 1) )and Mailer_Type name (ex. ppp) in each script.
3) Highlight the syntax from "Begin" to the end of script and change db to the db I am allowed to run, and then execute the script.
4) Copy the output result and paste it in my excel

I do this procedure from 2) to 4) for about 20 to 30 times a day.

I was wondering if there a way to control my 20 sql scripts and export those result in excel automatically ?
If is not possible to export those results in Excel automatically, Can I at least be able to write some kind of script to execute those my 20 scripts ?

Thank you so much for your help.

USE [dev_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ALT_Append_Fields]
AS

BEGIN
SET NOCOUNT ON;
DECLARE @File_Name varchar(50), @Mailer_Type varchar(50)

SET @File_Name = 'Your_mail_file_table_name_here'
SET @Mailer_Type = 'Choose_value_below'
--PPP
--ZZZ

DECLARE
@LoanCount int,
@LoanCounter int,
@acct_num numeric(9),
@workout_request_id numeric(9),
@SQLString varchar(max),
@SQL varchar(max),
@FINALSQL varchar(max),
@fl_acct_num varchar(50),
@Program varchar(50)

SET @fl_acct_num = 'acct_num'

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_schema = USER AND table_name = 'INPUT_APPEND')
DROP TABLE HMP_ALT_INPUT_APPEND

CREATE TABLE HMP_ALT_INPUT_APPEND
..............
.............
...........

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-07 : 10:14:09

have a look at SQLCMD

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources