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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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