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
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.
Number them and use a tool like http://code.google.com/p/simplescriptrunner/ or http://code.google.com/p/tarantino/
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
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.
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
