What I need to do is change a ActivePath entry in SQL that changes both in value and length to a different path of varying value and length then run this over and over until there are no more entries that match the ActivePath to be changed.
This is what I have so far and it works for single files where I already know the current file path, new file path, and the name of the file:
UPDATE [AuroraFileServer].[dbo].[File]
SET ActivePath = REPLACE(ActivePath, 'C:\ProgramData\MyData\FileServer\Data', 'C:\Videos\Archive 1')
WHERE ActivePath IN (SELECT ActivePath FROM [AuroraCore].[dbo].[DeviceEventFile] AS DEF
JOIN [AuroraCore].[dbo].[DeviceEvent] AS DE
ON DE.Id = DEF.DeviceEventId
JOIN [AuroraFileServer].[dbo].[File] AS F
ON DEF.FilePath = F.ActivePath
WHERE DE.Name LIKE 'ACBD13420160111185621001%')
So proof of concept works, but I need it to be much more dynamic as running this against hundreds or potentially thousands of videos would be impractical.
The old ActivePath location changes based on the date the video was uploaded to the system on a yyyy/mm/dd basis where the month and day can be one or two digit values depending on the month or day (1/1 versus 12/12, Jan 1st and Dec 12th respectively). The new ActivePath needs to be a different location, but the old ActivePath yyyy/mm/dd + 1 day as file archive from the primary location to the archive after just 24 hours.
So the process needs to take this:
C:\ProgramData\MyData\FileServer\Data\2016\1\13\ACBD13420160111185621001i100.avi
And change it to:
C:\Videos\Archive 1\2016\1\14\ACBD13420160111185621001i100.avi
For hundreds or thousands of entries in the db where, of course, nearly everything before the actual name of the file changes.
Is there a way to get this to work with say creating a table or index where all of the ActivePath entries can be dumped to that match the C:\ProgramData... path and have a query run against that file with a replace statement that uses a single line out of the index as the part to be replaced, replaces it, and then repeats over and over again working through the list until all of the entries have been replaced? I have seen other replace statements that follow this idea, but all of the old and new variables are known which is not my case.
I would think that I could run:
SELECT ActivePath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
And have that return the results to a table or index with everything, in my case (which will change outside of my test environment), after the 8th '\' being truncated off and duplicates removed for a list in which each line would be a separate entry into the first ' ' of the REPLACE statement. The second part of the REPLACE statement would need the date section of the original path copied + 1 day (so 31 +1 would need to change the month +1 to keep with how dates work). That gets the info we need to run the REPLACE statement which would loop back to to the beginning and repeat until all lines in that table or index have been worked through then stop. I just have no idea how to make that happen or where to begin.
So out of utilizing the function from bdn02, I have gotten closer to what I need. This is what I have so far:
(
@olddir varchar(300)
)
RETURNS varchar(300) AS
BEGIN
declare @tmpvar varchar(200)
declare @index int
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
declare @filename varchar(200)
declare @videodate datetime
declare @newpath varchar(300)
set @tmpvar = replace(@olddir, 'C:\ProgramData\MyData\FileServer\Data\', '')
set @index = charindex('\', @tmpvar)
set @year = substring(@tmpvar, 1, @index-1)
set @tmpvar = substring(@tmpvar, @index+1, len(@tmpvar)-@index)
set @index = charindex('\', @tmpvar)
set @month = substring(@tmpvar, 1, @index-1)
set @tmpvar = substring(@tmpvar, @index+1, len(@tmpvar)-@index)
set @index = charindex('\', @tmpvar)
set @day = substring(@tmpvar, 1, @index-1)
set @filename = substring(@tmpvar, @index+1, len(@tmpvar)-@index)
set @videodate = CONVERT (datetime, @day + '.' + @month + '.' + @year, 104)
set @videodate = DATEADD (day , 1 , @videodate)
--build new path
set @newpath = 'C:\Videos\Archive 1\' + cast(year(@videodate) as varchar) + '\' + cast(month(@videodate) as varchar) + '\' + cast(day(@videodate) as varchar) + '\'
return @newpath
END
That returns the new path when used with:
SELECT DISTINCT dbo.ConvertDir(ActivePath)
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
Good, now:
(
@olddir varchar(300)
)
RETURNS varchar(300) AS
BEGIN
declare @tmpvar varchar(200)
declare @index int
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
declare @filename varchar(200)
declare @videodate datetime
declare @oldpath varchar(300)
set @tmpvar = replace(@olddir, 'C:\ProgramData\MyData\FileServer\Data\', '')
set @index = charindex('\', @tmpvar)
set @year = substring(@tmpvar, 1, @index-1)
set @tmpvar = substring(@tmpvar, @index+1, len(@tmpvar)-@index)
set @index = charindex('\', @tmpvar)
set @month = substring(@tmpvar, 1, @index-1)
set @tmpvar = substring(@tmpvar, @index+1, len(@tmpvar)-@index)
set @index = charindex('\', @tmpvar)
set @day = substring(@tmpvar, 1, @index-1)
set @filename = substring(@tmpvar, @index+1, len(@tmpvar)-@index)
set @videodate = CONVERT (datetime, @day + '.' + @month + '.' + @year, 104)
--build new path
set @oldpath = 'C:\ProgramData\MyData\FileServer\Data\' + cast(year(@videodate) as varchar) + '\' + cast(month(@videodate) as varchar) + '\' + cast(day(@videodate) as varchar) + '\'
return @oldpath
END
Returns the old path when used with:
SELECT DISTINCT dbo.ConvertDir1(ActivePath)
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
Perfect, so now I have the old path and new path without duplicates.
Now I am trying to utilize a new function to bring the two udf's together in a replace statement with a WHERE loop. The problem is that I get "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.ConvertDir", or the name is ambiguous." in my new function for both dbo.ConvertDir and dbo.ConvertDir1. My default schema is dbo. Here is the function:
DECLARE @oldpath TABLE (old varchar(255))
DECLARE @newpath TABLE (new varchar(255))
INSERT INTO @oldpath (OLD)
SELECT DISTINCT dbo.ConvertDir1(oldpath);
INSERT INTO @newpath (NEW)
SELECT DISTINCT dbo.ConvertDir(newpath);
WHILE (1=1)
BEGIN
UPDATE f
SET f.ActivePath = REPLACE(f.ActivePath, o.old, n.new)
FROM AuroraFileServer.dbo.[File] AS f,
@oldpath AS o,
@newpath AS n
WHERE f.ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
IF @@ROWCOUNT = 0
BREAK
END
SELECT * FROM AuroraFileServer.dbo.[File]
What am I doing wrong?
INSERT INTO @oldpath (OLD)
SELECT DISTINCT dbo.ConvertDir1(oldpath);
Above was missing FROM and WHERE as well as not being optimized hence the SELECT TOP 1 instead of SELECT DISTINCT. This is what it needed:
INSERT INTO @oldpath (OldPath)
SELECT TOP 1 dbo.ConvertDir1(ActivePath) AS OldPath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
Now, the UPDATE f block was fine, but for it to loop and update every entry that had a path that matched the above INSERT INTO, each of those temp tables would have to be updated on each pass. This is what I came up with to accomplish that:
UPDATE @oldpath
SET OldPath = (SELECT TOP 1 dbo.ConvertDir1(ActivePath) AS OldPath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%')
So the way it works, which is how I envisioned it, is that the loop will update the temp tables with one single row for the path to look for/replace with, update the dbo.file table, then loop back to update the temp tables with the next path to look for/replace with before repeating over and over until the UPDATE f block no longer updates any entries. Once I got that working, I ported that over to update a very similar table.
Here is the full query:
DECLARE @oldpath TABLE (OldPath varchar(255))
DECLARE @newpath TABLE (NewPath varchar(255))
DECLARE @oldpath2 TABLE (OldPath2 varchar(255))
DECLARE @newpath2 TABLE (NewPath2 varchar(255))
INSERT INTO @oldpath (OldPath)
SELECT TOP 1 dbo.ConvertDir1(ActivePath) AS OldPath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
INSERT INTO @newpath (NewPath)
SELECT TOP 1 dbo.ConvertDir(ActivePath) AS NewPath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
INSERT INTO @oldpath2 (OldPath2)
SELECT TOP 1 dbo.ConvertDir1(FilePath) AS OldPath2
FROM AuroraCore.dbo.DeviceEventFile
WHERE FilePath LIKE 'C:\ProgramData\MyData\FileServer%'
INSERT INTO @newpath2 (NewPath2)
SELECT TOP 1 dbo.ConvertDir(FilePath) AS NewPath2
FROM AuroraCore.dbo.DeviceEventFile
WHERE FilePath LIKE 'C:\ProgramData\MyData\FileServer%'
WHILE (1=1)
BEGIN
UPDATE @oldpath
SET OldPath = (SELECT TOP 1 dbo.ConvertDir1(ActivePath) AS OldPath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%')
UPDATE @newpath
SET newpath = (SELECT TOP 1 dbo.ConvertDir(ActivePath) AS NewPath
FROM AuroraFileServer.dbo.[File]
WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%')
UPDATE @oldpath2
SET oldpath2 = (SELECT TOP 1 dbo.ConvertDir1(FilePath) AS OldPath2
FROM AuroraCore.dbo.DeviceEventFile
WHERE FilePath LIKE 'C:\ProgramData\MyData\FileServer%')
UPDATE @newpath2
SET newpath2 = (SELECT TOP 1 dbo.ConvertDir(FilePath) AS NewPath2
FROM AuroraCore.dbo.DeviceEventFile
WHERE FilePath LIKE 'C:\ProgramData\MyData\FileServer%')
UPDATE f
SET f.ActivePath = REPLACE(f.ActivePath, o.OldPath, n.NewPath)
FROM AuroraFileServer.dbo.[File] AS f,
@oldpath AS o,
@newpath AS n
WHERE f.ActivePath LIKE 'C:\ProgramData\MyData\FileServer%'
UPDATE def
SET def.FilePath = REPLACE(def.FilePath, o2.OldPath2, n2.NewPath2)
FROM AuroraCore.dbo.DeviceEventFile AS def,
@oldpath2 AS o2,
@newpath2 AS n2
WHERE def.FilePath LIKE 'C:\ProgramData\MyData\FileServer%'
IF @@ROWCOUNT = 0
BREAK
ELSE
CONTINUE
END
Big thumbs up to @bdn02 for helping out with the first function for me!