Link to home
Start Free TrialLog in
Avatar of pdegregorio
pdegregorio

asked on

How to run multiple .sql scripts from a single .sql script using sqlcmd

Hello,
I have several .sql scripts, each containing a create table statement and some stored procedure calls.
I need to execute the scripts in sequence and wish to do so in another .sql script so that all I have to do to create all of my tables is to run a single script. Every database I know of allows this somehow; I am asking how to do this in MS SQL Server 2005.
Thank you,
Peter
Avatar of QPR
QPR
Flag of New Zealand image

Type your statements into a new query and separate each with the word GO, save the script as an sql file and run as needed

e.g.

create table TestTable (
theID int,
theText varchar(100))
go
insert into testTable
(theID, theText)
values
(1, 'Hello World')
Go
create procedure mySP
@InputVar int
as
select * from TestTable
where theID = @inputVar
go
exec mySP 1
go
Avatar of pdegregorio
pdegregorio

ASKER

Thanks ... but I already have the CREATE TABLE statements in .sql files, one CREATE TABLE per .sql file. I'd like to run them in SQLCMD all at once, one after another by having it execute a script to do so.

For example, in Oracle one can use @ followed by a file name and it will execute that file. In Derby one can use Run followed by a file name. I know that MS SQL Server has an EXEC command but that appears to only apply to stored procedures. What I'm looking for is something like an EXEC command but that runs commands in an external file.
SOLUTION
Avatar of QPR
QPR
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I usually concatenate the scripts into a single file

type *.sql > script.txt

Then execute that either as a query or via osql.

If the dcripts aren't in alphabetical order or in different folders then you can create a batch file to concatenate them

set fdest=release.sql

echo use Billing > "%fdest%"
echo go  >> "%fdest%"
echo.  >> "%fdest%"

      set fpath=tables\
            CALL :concat "%fpath%" "%fdest%" SystemData.sql
            CALL :concat "%fpath%" "%fdest%" BillRun.sql
            CALL :concat "%fpath%" "%fdest%" FileBatch.sql
      
      set fpath=procs\
            CALL :concat "%fpath%" "%fdest%" s_ProcessFile.sql
            CALL :concat "%fpath%" "%fdest%" s_ProcessAllFilesInDir.sql

goto :end

:concat
      echo. >> %2
      echo print 'processing file - %3 ' >> %2
      type %1%3 >> %2

:end



You can also do this from a stored proc using multiple osql statements - usually working on a file that contains a list of files.
I'm going make a .bat file with repeated calls to sqlcmd (as described in accepted answer 1) so i can get separate log files. I tested accepted answer 2 and it works exactly as described. -- Thanks everyone, good answers.