sql-serversql-server-2008t-sqlosql

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

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?

Solution

  • 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;