SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 execute multiple scripts and export results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbonneau
Yak Posting Veteran

50 Posts

Posted - 11/06/2012 :  20:18:50  Show Profile  Reply with Quote
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

Edited by - dbonneau on 11/06/2012 20:22:05

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2012 :  10:14:09  Show Profile  Reply with Quote
have a look at SQLCMD

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000