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 |
---|---|---|
1234500 | 091519951234500.jpg | 091519951234500.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 |
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 | ID | Action | Month | When | Year |
---|---|---|---|---|---|
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 |
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\ |
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!