Calling SQL Scrips from a sql script!

Question

I am just wondering if it is possible to call multiple SQL scripts from a single startup.sql script.

i.e.

StartUp.sql calls:

CreateDatabase.sql CreateStoreProcedureFirst.sql CreateStoreProcedureSecond.sql InsertDummyValues.sql otherscripts.sql... etc

At the moment I am loading and running each file one at a time.. there are more scripts I run also and sometimes miss a script or do it in the wrong order!

Hope this meakes sense

Thanks

Answer 1

Number them and use a tool like http://code.google.com/p/simplescriptrunner/ or http://code.google.com/p/tarantino/

Answer 2

This tip may help you out:

http://www.devx.com/tips/Tip/15132

Answer 3

Both Oracle and MySQL have command line tools and from experience i know you can run them thus

mysql database_name < yoursql.sql > output.tab

however this means running from the CLI not from an original SQL statement so may not be what you are looking for. I don't believe you can make calls to the system from MySQL

Answer 4

mcintyre321's links look handy...

I used powershell to do something similar. I'd name all of my setup scripts with a prefix and a 1.x in whatever order they needed to be compiled.

I then named all my teardown scripts 3.x in the proper order.

The command in cmd window:

PS builddir:\> .\buildsql.ps1 -currentbuilddir "C:\Documents and Settings\SGreene\My Documents\svn\Ticketing" -buildfile "sqlbuild.sql" -teardownfile
"teardown.sql"

The powershell script (buildsql.ps1)

param($currentbuilddir,$buildfile1,$teardownfile)

new-psdrive -name builddir -PSProvider filesystem -Root (resolve-path $currentbuilddir)

cd builddir:

rm $buildfile1
rm $teardownfile


Get-item COM_ENCRYPT_1* | ForEAch-object {cat $_ >> $buildfile1; "GO --SYSTEM INSERTED GO--------------" >> $buildfile1} 

Get-item COM_ENCRYPT_3* | ForEAch-object {cat $_ >> $teardownfile; "GO --SYSTEM INSERTED GO------------" >> $teardownfile} 

My first time doing this, but it seemed to work OK.

Answer 5

Yes - you can use SQLCMD (SQL Server 2005 or later) to make T-SQL scripts a little more flexible, with the equivalent of includes and basic variables.

The SQLCMD command for include is :r as in:

:r c:\someFolder\script1.sql
:r c:\someFolder\script2.sql

etc.

See http://www.mssqltips.com/tip.asp?tip=1543

Answer 6

Have you used or heard of dynamic sql? This is how i would do it...

DECLARE @SQL1PROCEDURE nvarchar(4000) DECLARE @SQL2PROCEDURE nvarchar(4000)

SET @SQL1Procedure = ' CREATE PROC sp1 AS BEGIN blah blah blah END '

SET @SQL2Procedure = ' INSERT DUMMY TABLE (FIELD1,FIELD2) VALUES (VALUE 1,VALUE2)'

EXEC @SQL1Procedure EXEC @SQL2Procedure