Running SQL Files from other SQL Files with MSSQL

During my work with RoundhousE, I wanted to ensure the SQL scripts ran in the order I wanted. Foreign keys referenced things which had to exist first (for example an account has to belong to a customer, so the customer has to be created first).

RoundhousE, within a given folder, will just run scripts in alphabetical order. So you could name them “0001….sql”, “0002….sql” but what happens if you have 100 scripts and you want to insert one in the middle? Then renaming becomes a pain. Instead, we write one ‘master’ SQL file which will call the others (note - RoundhousE will only load SQL scripts, which is why I had to do it like this).

The data scripts I had generated were just lots of “insert …” statements, so nothing complicated. But this was a slightly more difficult problem to solve than I first expected.

Solution

Essentially we want to read in the file as a string, and then execute it as dynamic SQL.

Loading the file

DECLARE @FileContents  NVARCHAR(MAX)


SELECT @FileContents=BulkColumn
FROM   OPENROWSET(BULK'C:\temp\myfile.sql',SINGLE_NCLOB) x;

A CLOB is a Character Large Object in SQL, basically a huge list of characters. NCLOB is the same but in nvarchar format.

You might now see this message:

Error Number: 4809, Message: SINGLE_NCLOB requires a UNICODE (widechar) input file. The file specified is not Unicode.

OPENROWSET wants the files you read to be in Unicode, specifically UTF-16. I use Notepad++, so I clicked Encoding -> ‘Encode in UCS-2 BE BOM’ (which is essentially equivalent to UTF-16).

With that done, the file loads successfully.

Now we run it:

exec sp_executesql @FileContents

And your SQL in the other file (myfile.sql) should be run.

You can add multiple commands like this to run any SQL file you want (provided the encoding is correct!).

I thought this problem would be way easier than it turned out to be, so writing it up in case anyone else comes across this.