sql server - Is it possible to execute a text file from SQL query?

Question

I have a number of generated .sql files that I want to run in succession. I'd like to run them from a SQL statement in a query (i.e. Query Analyzer/Server Management Studio).
Is it possible to do something like this and if so what is the syntax for doing this?

I'm hoping for something like:

exec 'c:empfile01.sql' 
exec 'c:empfile02.sql'

I am using SQL Server 2005 and running queries in management studio.

question from:https://stackoverflow.com/questions/241925/is-it-possible-to-execute-a-text-file-from-sql-query

Answer 1

use xp_cmdshell and sqlcmd

EXEC xp_cmdshell  'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName