We help IT Professionals succeed at work.

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

pdegregorio
pdegregorio asked
on
2,157 Views
Last Modified: 2012-06-27
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
Comment
Watch Question

QPR

Commented:
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

Author

Commented:
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.
QPR
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.