kevinbwood@koalacomputers.com

[ Welcome | Articles ]

Need a batch file or some T-SQL, write it with T-SQL

    Years ago, I had millions of files in two different directories. (No, not folders within those directories.) (And this was a system I 'inherited'.) Fortunately, the file name included a date, i.e. 092319951234567.jpg (so I knew the image was created September 23, 1995. The path and file name were in sql server. The goal, move the files into subdirectories. The obvious destination would be folders based on the year and month.

    Using queries based on the name of the file (a string within the table) it would be easy to determine the count of images by month and year. (For this article, I am using only a subset of the rows and columns. Sample Data (subset)

ID PHOTO SIGNATURE
1234500091519951234500.jpg091519951234500.tiff
1234501 091519951234501.jpg 091519951234501.tiff
1234502 091519951234502.jpg 091519951234502.tiff
1234503 091519951234503.jpg 091519951234503.tiff
1234504 091519951234504.jpg 091519951234504.tiff
1234505 091519951234505.jpg 091519951234505.tiff
1234506 091519951234506.jpg 091519951234506.tiff
1234507 091519951234507.jpg 091519951234507.tiff
1234508 102319961234508.jpg 102319951234508.tiff
1234509 102319961234509.jpg 102319951234509.tiff
1234510 102319961234510.jpg 102319951234510.tiff
1234511 102319961234511.jpg 102319951234511.tiff
1234512 102319961234512.jpg 102319951234512.tiff
1234513 102319961234513.jpg 102319951234513.tiff
1234514 102319961234514.jpg 102319951234514.tiff
1234515 102319961234515.jpg 102319951234515.tiff
1234516 102319961234516.jpg 102319951234516.tiff
1234517 102319961234517.jpg 102319951234517.tiff
1234518 102319961234518.jpg 102319951234518.tiff
About this time, I read how you could actually return a set string with an SQL query. . .

SELECT 'the ID ' AS What, ID, ' was printed on ' AS [Action], SubString(photo,1,2) AS [Month], ' in the year ' AS [When], SubString(photo,4,4) AS [Year]
FROM IDs

(Note: if you want to try this at home with MsAccess, use mid instead of substring)

The results:

What IDActionMonthWhenYear
the ID 1234500 was printed on 09 in the year 1995
the ID 1234501 was printed on 09 in the year 1995
the ID 1234502 was printed on 09 in the year 1995
the ID 1234503 was printed on 09 in the year 1995
the ID 1234504 was printed on 09 in the year 1995
the ID 1234505 was printed on 09 in the year 1995
the ID 1234506 was printed on 09 in the year 1995
the ID 1234507 was printed on 09 in the year 1995
the ID 1234508 was printed on 10 in the year 1996
the ID 1234509 was printed on 10 in the year 1996
the ID 1234510 was printed on 10 in the year 1996
the ID 1234511 was printed on 10 in the year 1996
the ID 1234512 was printed on 10 in the year 1996
the ID 1234513 was printed on 10 in the year 1996
the ID 1234514 was printed on 10 in the year 1996
the ID 1234515 was printed on 10 in the year 1996
the ID 1234516 was printed on 10 in the year 1996
the ID 1234517 was printed on 10 in the year 1996
the ID 1234518 was printed on 10 in the year 1996
Given that there were millions of images to be moved, and the fact I am efficient (or lazy) I wondered if I could write the script to move the files with TSQL. (Spoiler alert, you can)

SELECT
'copy C:\Myfiles\'+[photo] AS [from],
'C:\MyFiles\YearMonth\'+ SubString(photo,5,4)+'\'+SubString(photo,1,2)+'\' FROM IDs

Note: some of you are snickering, thinking "If he were smart, he would use something like robocopy. that way if he has to start over, only new or changed files would be copied."
yes. that is a prudent modification of this. Feel free to edit as you need.

The Result:

from (No column name)
copy C:\Myfiles\091519951234500.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234501.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234502.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234503.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234504.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234505.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234506.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\091519951234507.jpg C:\MyFiles\YearMonth\1995\09\
copy C:\Myfiles\102319961234508.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234509.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234510.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234511.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234512.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234513.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234514.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234515.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234516.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234517.jpg C:\MyFiles\YearMonth\1996\10\
copy C:\Myfiles\102319961234518.jpg C:\MyFiles\YearMonth\1996\10\
As you can see, it accurately parses out the year and month. I can save the results and run it as a batch file.

In this example, I use the copy command (so I can review the results. I can compare the number of source and destination files.

Years later, I was working with a client who needed to change all his databases to simple recovery model before an upgrade, then change back to full recovery after the upgrade. Thinking that if I could write a script with TSQL, could I write TSQL with TSQL? This would us hours of work changing hundreds of databases back and forth. (Since each Site had their own naming conventions and databases were created quarterly, I also wanted a script that could identify the databases I wanted without knowing the names in advance.)

But that is a script for another day!


Go to top