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

run sql script from command line linux
how to run sql script from command line
mysql execute sql file
run sql script from batch file sql server
mysql run sql file from terminal
sql query to read data from text file
run sql file from command line
a file containing sql statements for execution

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:\temp\file01.sql' 
exec 'c:\temp\file02.sql'

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

use xp_cmdshell and sqlcmd

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

4.5.1.5 Executing SQL Statements from a Text File, 4.5.1.5 Executing SQL Statements from a Text File. The mysql client typically is used interactively, like this: shell> mysql db_name. However, it is also possible to​  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

Very helpful thanks, see also this link: Execute SQL Server scripts for a similar example. To turn xp_cmdshell on and off see below:

On

SET NOCOUNT ON  
EXEC master.dbo.sp_configure 'show advanced options', 1 
RECONFIGURE 
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 
RECONFIGURE 

Off

EXEC master.dbo.sp_configure 'xp_cmdshell', 0 
RECONFIGURE 
EXEC master.dbo.sp_configure 'show advanced options', 0 
RECONFIGURE  
SET NOCOUNT OFF 

Execute text file in sql server, You need to break the script on the GO commands. Read this  The procedure does not gather any of the text to be written to the file. Its sole purpose is to take the text gathered elsewhere and passed to it, and write it to the designated file (including the path). The procedure leverages an extended stored procedure named xp_cmdshell. You expose your SQL Server to hackers or others with ill intent by leaving xp_cmdshell active.

This is what I use. Works well and is simple to reuse. It can be changed to read all files in the directory, but this way I get to control which ones to execute.

/*  
execute a list of .sql files against the server and DB specified  
*/  
SET NOCOUNT ON  

SET XACT_ABORT ON  
BEGIN TRAN  

DECLARE @DBServerName   VARCHAR(100) = 'servername'  
DECLARE @DBName VARCHAR(100) = 'db name'  
DECLARE @FilePath   VARCHAR(200) = 'path to scrips\'  
/*

create a holder for all filenames to be executed  

*/  
DECLARE @FileList TABLE (Files NVARCHAR(MAX))  

INSERT INTO @FileList VALUES ('script 1.sql')  
INSERT INTO @FileList VALUES ('script 2.sql')  
INSERT INTO @FileList VALUES ('script X.sql')  

WHILE (SELECT COUNT(Files) FROM @FileList) > 0  
BEGIN  
   /*  
   execute each file one at a time  
   */  
   DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) Files FROM @FileList)  
   DECLARE @command  VARCHAR(500)  = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i "' + @FilePath + @Filename +'"'  
   EXEC xp_cmdshell  @command   

   PRINT 'EXECUTED: ' + @FileName     
   DELETE FROM @FileList WHERE Files = @FileName  
END  
COMMIT TRAN  

Create Batch File to Export SQL Query Results to a Text File, In this post, I'm going to show you how to create a batch file to export SQL query results to a Text file, and add a button in Access to run the batch file. However you can redirect the results of the query along with the messages to a file instead if you want. Hit ctrl-F before running the query and this redirect the results to a file. SQL Server directs the results to 1) Grid (ctrl-D), 2) Text (ctrl-T) and 3) File (ctrl-F)

Or just use openrowset to read your script into a variable and execute it (sorry for reviving an 8 years old topic):

DECLARE @SQL varchar(MAX)
SELECT @SQL = BulkColumn
FROM OPENROWSET
    (   BULK 'MeinPfad\MeinSkript.sql'
    ,   SINGLE_BLOB ) AS MYTABLE

--PRINT @sql
EXEC (@sql)

4.5.1.5 Executing SQL Statements from a Text File, 4.5.1.5 Executing SQL Statements from a Text File. The mysql client typically is used interactively, like this: shell> mysql db_name. However, it is also possible to​  The EXECUTE MySQL command can only be used for one prepared statement. If case you want to execute multiple queries from the string, consider saving them into file and source it, e.g. SET @query = 'SELECT 1; SELECT 2; SELECT 3;'; SELECT @query INTO OUTFILE '/tmp/temp.sql'; SOURCE /tmp/temp.sql;

I wouldn't recommended doing this, but if you really have to then the extended stored procedure xp_cmdshell is what you want. You will have to first read the contents of the file into a variable and then use something like this:

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

Note: xp_cmdshell runs commands in the background, because of this, it must not be used to run programs that require user input.

Execute MySQL statements from a text file, It's possible to execute statements in MySQL from a text file from the command line or from the The text file must contain SQL statements for this to work, e.g.: Drag Execute SQL Task to Control Flow Pane and then configure as shown in Figure below. As we are using the .sql file to run , so instead of direct input we used file connection and then made connection to one of the .sql file.

How to run SQL statements from a text file, You may find that you need to execute SQL statements that exist in a .sql file. There are a few different ways to do this depending on what you  DTS Transfer Data Task - output to text file with field names infirst line; Perl code for parsing a text file and output a text file; Concatenate all records in 1 Field and output in Text file; Output to text-file; How to load and output a text file in C? In Clause - to query text file; xml/xslt, .net 2.0, whitespace, output to text file

8 Ways to Export SQL Results To a Text File – SQLServerCentral, We can export the SQL Server query results to a txt file by executing the following cmdlets: Invoke-Sqlcmd -InputFile "C:\sql\myquery.sql"  If you restore the contents of an SQL file to a suitable SQL RDBMS (relational database management server) … then they are in “tables” already (at least conceptually, in terms of how your queries access them).

Running SQL queries on plain text files, The following section shows how to run SQL queries on a log file generated by Microsoft IIS Server. To convert the text file in an SQL-friendly format, we remove the first three file. Following table shows the possible values this tag accepts. yes you can, i see the query you posted above, you can put that into.txt file and then in vbscript you can read the contents of the txt file and store that into a string and use that a sql query. not sure if sqlcmd.exe will work against oracle db, but i have used sqlcmd.exe before and there is an o[ption to specify the the query file seperately.

Comments
  • Is it possible to excaute that on sql Azure as well ?
  • Good solution I also combined Archi Moore's answer to enable and disable the xp_cmdshell feature
  • I do not know what quick search shows but OSQL is deprecated in favor of SQLCMD starting from SQL Server 2005. Running OSQL even on machine with SQL Server 2005 shows: "Note: osql does not support all features of SQL Server 2005. Use sqlcmd instead. See SQL Server Books Online for details"
  • This worked for me, I just had to update "localhost" to our SQL Server address and "NorthWind" to the database name. Both text strings are easily available in SSMS. Thank you!