How to import many Stored Procedure scripts

[tosberg]

Here's the skinny:
I wrote about 200+ Stored Procedures and I want to import them into a SQL SErver DAtabase. How can I get all of these scripts into SQL Server without having to copy and paste every single one of them. Is there some kind of command line utility or import procedure where I can import all of them at once? Thanks!

 

[MeanGreen]

If you have them in the Create Procedure format, just append them all together and run the entire SQL text within Query Analyzer and it will create them all in one pass. If you already have them on your development platform, just go into Enterprise Manager, highlight the stored procedures and use the "Generate SQL Scripts".

Hope this helps.

 

[cdukes]

Aletrnativly,

You can create a batch file and run each stored procedure through ISQL

eg.

@echo off

Rem /* ------------------------------------------------------------ */
Rem /* */
Rem /* L01_Add.cmd */
Rem /* */
Rem /* Script to create Add stored procedures for the LOP Database */
Rem /* */
Rem /* %1 = Server Name */
Rem /* %2 = sa password (must not be blank) */
Rem /* [%3] = stored procedure (blank = all) */
Rem /* */

if "%1"=="" goto message1
if "%2"=="" goto message2
if not "%3"=="" goto load_sp
: load_all_sps
Title SQL Build
Echo Rebuilding standard system stored procedures...
Echo ...
for %%f in (
sp_getemployeeid
sp_getnamefromaccountid
sp_getprimaryid
sp_getteamreference
sp_gmupdclient
sp_gmupdenduser
sp_gmupdextcompany
sp_gmupdextcompanycontact
sp_gmupdoffer
sp_setupnewjobcount
sp_setupnewsegmentcount
sp_updselectionstatus
spc_Clients
spc_employee
spc_EndUser
spc_DataCardValue
spc_extcompany
spc_extcompanycontact
spc_geocriterion
spc_job
spc_mailingfilespecs
spc_MediaCombForEngineVer
spc_MediaSpecification
spc_ModelOnSegSpec
spc_offer
spc_order
spc_pricingdetailsforjob
spc_pufile
spc_puspecification
spc_RateDataAssociations
spc_SortSpecification
spc_segmentspecification
sps_getsegrefupdatelist
) do @%0 %1 %2 %%f

goto end
: load_sp

title SQL Build - %3
Rem Echo ...
Echo %3

isql /S %1 /d Unity /U sa /P %2 /q /i %3.sql

goto end

: message1
Echo Please enter a server name
Echo Syntax: ReformatProcs {Server} {sa Password} [{stored procedure}]
Pause
goto end

: message2
Echo Please enter the sa password
Echo Syntax: ReformatProcs {Server} {sa Password} [{stored procedure}]
Pause
: End


Hope this helps,

Chris Dukes