Last Updated: February 25, 2016

Multiple SQL column delete and create using Regex

I recently had a task to move a large number of columns from one table to another and as I'm not keen on writing out line after line of SQL I generated some with a bit of regex. Here is a C# script I put together in LINQPad to do the replace (but you could easily do them in notepad).

void Main()
{
 string sourceTable = "Table1";
 string sourceTableFK = "Table2Id";
 string targetTable = "Table2";
 string targetTablePK = "Id";

 Dictionary<string, string> columns = new Dictionary<string, string> { 
 { "ColumnA", @"NVARCHAR(255)"}, 
 { "ColumnB", @"DATETIME"},
 { "ColumnC", @"INT"}
 };

 string addReplace = "IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE Name = '1ドル' AND object_id = (SELECT object_id FROM sys.objects WHERE Name = '" + targetTable + "'))\r\nBEGIN\r\n\tALTER TABLE " + targetTable + "\r\n\tADD\t1ドル 2ドル\r\nEND\r\nGO";

 string migrateReplace = "IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE Name = '1ドル' AND object_id = (SELECT object_id FROM sys.objects WHERE Name = '" + sourceTable + "'))\r\nBEGIN\r\n\tUPDATE "+ targetTable + "\r\n\tSET\t1ドル = (SELECT 1ドル FROM " + sourceTable + " WHERE " + sourceTableFK + " = " + targetTable + "." + targetTablePK + ")\r\nEND\r\nGO";

 string dropReplace = "IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE Name = '1ドル' AND object_id = (SELECT object_id FROM sys.objects WHERE Name = '" + sourceTable + "'))\r\nBEGIN\r\n\tALTER TABLE " + sourceTable + "\r\n\tDROP COLUMN 1ドル\r\nEND\r\nGO";

 foreach (var column in columns)
 {
 Regex.Replace(string.Format("{0}|{1}", column.Key, column.Value), @"^([^\|]*)\|([^\|]*)$", addReplace).Dump();
 Regex.Replace(column.Key, @"^([a-zA-Z0-9]*)$", migrateReplace).Dump();
 // handle constraints here
 Regex.Replace(column.Key, @"^([a-zA-Z0-9]*)$", dropReplace).Dump();
 }
}

The generated SQL will: