Equivalent of MySQL's SOURCE command to load a script for TSQL?

Question

In MySQL, you can issue commands like this:

drop database foo;
create database foo;
use foo;
source c:\src\bar.sql

The source statement loads a external script and runs it.

What's the equivalent in SQL Server?

Answer 1

Possibly something like:

EXEC master..xp_cmdshell 'sqlcmd -S . -E -d "database name" -i "c:\src\bar.sql"';

If you want a specific SQL auth account instead, you can say:

EXEC master..xp_cmdshell 'sqlcmd -S . -U user -P pass -d db -i "c:\src\bar.sql"';

If you want this to be portable and not hard-code the instance name (the above assumes a default instance) then:

DECLARE @sql NVARCHAR(MAX) = N'sqlcmd -S "' + @@SERVERNAME + '" -E'
    + ' -d db -i "c:\src\bar.sql"';

EXEC master..xp_cmdshell @sql;