SQLCMD - Being able to run mutliple .SQL files

Recently I came across a scenario that required me to generate and keep individual .SQL files. However, I also needed to run them in a certain order all the time there was a change to the database schema.

I found a way to run them at one time versus running them individually, using the new MicroSoft SQL Server Management Studio (express) for SQL 2005 db server.

Although the instructions do provide the commands allowed one thing I had to figure out on my own was the syntax for the comman :r.

The command :r is the command which you can use to run a .SQL file by specifying a path to the file. However, in my case I had spaces in the directory path. (i.e. C:\Filepath\file name.sql). For this reason I had to enclose the path with double quotes. (i.e. :r "C:\Filepath\file name.sql").

You will need to write one :r command for each .sql file you want to execute. In my case there were 4 .sql files. This reduced the amount of steps I had to perform everytime I had a schema change.

Update I have found that altough this provides for convenience it does not parse the files correctly if the files contain appostrophies/ or single quotes ( ' ) in the values of a query. For example, I had some insert statements that contained values like O''Grady. The additional bolded appostrophy is required in order for the parser to recognize the following character as part of the value, instead of termination dillimeter of the query. Or at least I thought. When I ran the query in MicroSoft SQL Server Management Studio (express) it ran fine. When I executed the file, following the instructions above in my original blog, I found the values that were being inserted were infact incomplete. In the example of the O'Grady insert statement, it inserted Orady instead. As to why this happens, well my assumption is that the parser that SQLCMD uses is not the same that MSSMS uses. I could be wrong but after this disappointment I didn't want to spend more time then I had to.

I still use the SQLCMD for .SQL files that don't have query values with appostrophies. However, I try to keep all the queries that do have appostrophies and run them in the query window. Still reduces the amount of steps but not down to one as I was hoping.

Generating SQL Insert Statements From A Data Table

Every now and then one is faced with a situation where they have to write and execute insert statements with existing tables and data. Having to personally deal with this before, a friend of mine shed some light as to how to generate SQL Insert statements. This comes in handy when you may not have access to tools, such as Microsoft's Enterprise Manager for SQL server or of the sort. Below is an example of such a solution.

The following is a simple example, but it can be used as a guide to generate various queries.

select('Insert into TableName + '(' + TableColumnName1,TableColumnName2,TableColumnName3 + ')' + values(''' + TableColumnName1 + ''',''' + TableColumnName2 + ''',''' + TableColumnName3 + ''')') from TableName

The basic principle is concatenating strings. The text in italics serve as parameters, which represent your table's name and columns.

In my case, I used this to generate an insert statment for each row in a static table. The output to this looked something like the following.

Insert into TableName (TableColumnName1, TableColumnName2, TableColumnName3) values(' TableColumnName1','TableColumnName2','TableColumnName3') from TableName

As simple as this may seem, there's a few things to consider.

• After you run this script, if you have data that contains appostrophies, you will need to manually add the proper set of single quotes to the output in order to escape those characters.


• The opposite is true when dealing with Integer types. You don't need to add the single quotes within the script. Update The example as is will not work for Integers or any other type other then varchars. You will encounter an invalid casting message. I have yet to solve this one as well.


• This will not work with tables that contain nulls. The script will abend once it encounters nulls.

If anyone can post a solution to this, I would highly appreciate it.