Woo.. I love *NIX, especially ‘sed’ (and awk too for that matter). Wonderful things you can do to automated your programming, speed up development and really REALLY cut down on cut-paste errors.
Today’s task, was to take this list of tables… and generate a single bit fat T-SQL command to truncate them all.. *however* these tables might not exist in a given environment so each Truncate is wrapped in a test to make sure the table is there. If it’s not there, nothing to do, not to mention avoid a potential fatal error trying to truncate a non-existent table.
Here is a snippet of the tables in question:
…
FCT_CRIMINOGENIC_NEED_TOP_DOMAINS
FCT_DETENTION_RISK_RESPONSE_DETAILS
FCT_DETENTION_RISK_RESPONSE_FULL
…
This is the desired T-SQL for these three tables
— Truncate table FCT_CRIMINOGENIC_NEED_TOP_DOMAINS
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ‘FCT_CRIMINOGENIC_NEED_TOP_DOMAINS’)
BEGIN
TRUNCATE TABLE FCT_CRIMINOGENIC_NEED_TOP_DOMAINS
END— Truncate table FCT_DETENTION_RISK_RESPONSE_DETAILS
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ‘FCT_DETENTION_RISK_RESPONSE_DETAILS’)
BEGIN
TRUNCATE TABLE FCT_DETENTION_RISK_RESPONSE_DETAILS
END— Truncate table FCT_DETENTION_RISK_RESPONSE_FULL
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ‘FCT_DETENTION_RISK_RESPONSE_FULL’)
BEGIN
TRUNCATE TABLE FCT_DETENTION_RISK_RESPONSE_FULL
END
The Sed Command
sed ‘s#^\(.*\)#– Truncate table \1 \’$’\nIF EXISTS (SELECT * FROM sys.objects WHERE NAME = \’\\1\’) \\’$’\n BEGIN \\’$’\n TRUNCATE TABLE \\1 \\’$’\nEND \\’$’\n#g’ MY_INPUT_FILE
Secret Sauce Ingredients
Setting up the string match. In this case it was simple.. I wanted the entire line which is represented with .*
\(.*\)
This loads the matching string into an internal register in ‘sed’ that can be references as ‘1’ within the output replacement expression. This is what it looks like in the command:
\1
Adding a newline in the output.. this was the big trick, and requires dropping into the shell to generate the desired output. The string in the command looks like this:
\’$’\n
You may notice after the first drop into the shell, the escaping has to be doubled for the substitution and the newline…
\\1 \\’$’\n
I cannot speak to the reason for this, but that is what I encountered and why the substitutions change in the latter part of the string. You may find that is not necessary, or might cause an issue in your environment. Adjust as necessary.