SQL Tricks

There's a lot you can do with SQL that some developers don't realise. Here's a few things you might not have thought of...

Changing Text Columns en Mass

Example. You have a column that contains file names so that an application can find them on the disk. You want to move the files to a different drive and path, and that would mean updating 1000's of rows to set the new one. You can use SQL to do this nicely much of the time.

update projects set file_output_folder = replace(file_output_folder,'D:\\Data','C:\\DDrive\\Data');

Generating DOS/Linux Batch Files using SQL

Say you need to now create the folders on the disk, cause they don't exist yet. You can use the SQL to generate a DOS batch file like this...

select distinct
concat(
replace(
file_output_folder,
'D:\\Data','mkdir "C:\\DDrive\\Data'
),
'"'
)
from projects;

Sample of Output

mkdir "C:\DDrive\Data\Database\Indexer Output\Blah\XmlToUpload"
mkdir "C:\DDrive\Data\Database\Indexer Output\XML for Transfer"
mkdir "C:\DDrive\Data\Database\Indexer Output\XML for DB Import"
mkdir "C:\DDrive\Data\Database\Indexer Output\test"

Using SQL to generate files like this can save you lots of time!

Using SQL to create more SQL

Example: Someone has written 100 stored procedures that bulk load some data from tables in another database. Rather than painfully triggering them one-by-one, you want to write a new stored procedure that runs them all at once.

You can use an SQL statement to generate another one that you can run. This kind of trick can save you lots of typing!

Some SQL like this will do it...

/* Assumes that the 100 sprocs someone made all start with spLoadTable */
select 'create procedure spLoadAllData as'
union
select concat(concat('exec ', routine_name),';')
from information_schema.routines
where routine_name like 'spLoadTable%'

The output might be something like this:

create procedure spLoadAllData as
exec spLoadTableCustomers;
exec spLoadTableOrders;
exec spLoadTableLineItems;
...96 more!

You can then copy and paste into a new SQL window, tweak it as you see fit, and then run it. You don't really need the union statement at the top, but it does demonstrate how you can add lines to a query.