How to call a SQL script from within another SQL script?

Question

i want to call a series of .sql scripts to create the initial database structure

1. script1.sql
2. script2.sql etc.

is there any way of doing this without sqlcmd or stored procedures or any other kind of code that is not sql ? just inside a .sql file.

Answer 1

Sure. Just create a little app that pulls in all the .sql files you want and executes them. Do it in VB.NET as follows:

Sub ExecuteSqlScript(FilePath As String)

    Dim Script As String
    Dim FileNumber As Integer
    Dim Delimiter As String
    Dim aSubscript() As String
    Dim Subscript As String
    Dim i As Long

    Delimiter = ";"
    FileNumber = FreeFile
    Script = String(FileLen(FilePath), vbNullChar)

    ' Grab the scripts inside the file
    Open FilePath For Binary As #FileNumber
    Get #FileNumber, , Script
    Close #FileNumber

    ' Put the scripts into an array
    aSubscript = Split(Script, Delimiter)

    ' Run each script in the array
    For i = 0 To UBound(aSubscript) - 1
        aSubscript(i) = Trim(aSubscript(i))
        Subscript = aSubscript(i)
        CurrentProject.Connection.Execute Subscript

    Next i

End Sub

Example from: http://snipplr.com/view/3879/run-sql-script-from-external-file/

Answer 2

you could try this:

exec master..xp_cmdshell 'osql -E -ix:\path\filename.sql'

osql must be in the path, the full filename must be known, and logins have to be set up correctly (options -E or -U)

Answer 3

There's no reason to exclude stored procedures. You don't need to include "any other kind of code that is not sql", plus

EXEC someothersp

which will be required (or its equivalent) in any other solution.

What's your reason for excluding them? I would sure think it beats writing code in yet another language.