Execute Script From A File

This script shows how to load a SQL script file into a nvarchar(max) local variable and then execute it.


-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
The script must not contain a GO Statement
Runs in SQL Server 2005
*/

set nocount on
if object_id('tempdb..#FileData','U') is not null
begin drop table #FileData end

create table #FileData ( FileData nvarchar(max) )

declare @FileName varchar(255)
declare @SQLLoad nvarchar(max)
declare @Script nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:MyDirMyScriptFile.sql'

-- Create command to load the file into temp table #FileData
set @SQLLoad =
N'insert into #FileData
select fd.*
from openrowset(bulk ''' + @FileName + ''',SINGLE_CLOB ) as FD'

-- Load file data into temp table #FileData
exec sp_executesql @SQLLoad

-- Load SQL into a local variable from temp table #FileData
select top 1 @Script = FileData from #FileData

-- Execute the script
exec sp_executesql @Script





CODO ERGO SUM