Using user-defined functions to dynamically update temp tables and database table entries

Question

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.

EDIT:

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?

I tink that is more simple to write a specific function that get the old path as input, parse it, add a day and return the new path. You can use it directly in a select — bdn02, Jan 14 '16 at 20:52
I might need some further explanation, but let me do some research into that approach and see what I come up with. — Trevor, Jan 14 '16 at 21:04

score 0 · Answer 1 · answered Jan 14 '16 at 21:46

I wrote a function, seems that works. Maybe not perfect....

CREATE FUNCTION ConvertDir ( @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) + '\' + @filename return @newpath END

Call it: select dbo.ConvertDir('C:\ProgramData\MyData\FileServer\Data\2016\1\13\ACBD13420160111185621001i100.avi')

You may use the function in a select or a view

Alright, so that definitely returns the correct new path for single file paths. Now, I am trying to figure out how to make that run against all of the different file paths in the File table that have the "C:\ProgramData\..." without me having to manually find each file path and enter it into the query then build a REPLACE statement before repeating the process again. Any idea how to go about this? — Trevor, Jan 14 '16 at 22:08
SELECT ActivePath, dbo.ConvertDir(ActivePath) FROM AuroraFileServer.dbo.[File] WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%' — bdn02, Jan 14 '16 at 22:28
Okay, so modifying the above function to return the old path without the file name and using the query as so: SELECT DISTINCT dbo.ConvertDir(ActivePath), dbo.ConvertDir1(ActivePath) FROM AuroraFileServer.dbo.[File] WHERE ActivePath LIKE 'C:\ProgramData\MyData\FileServer%' gets me the old and new paths as they would be needed by the REPLACE statement without duplicates. Now, how would I use that query to get the results into a REPLACE statement? — Trevor, Jan 14 '16 at 22:45
Will the idea on [THIS](http://stackoverflow.com/questions/12369694/dynamic-sql-search-replace-function) page apply here? — Trevor, Jan 14 '16 at 23:01

score 0 · Accepted Answer · answered Jan 18 '16 at 16:44

Okay, I figured it out!

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!

Using user-defined functions to dynamically update temp tables and database table entries

EDIT:

2 Answers2

Okay, I figured it out!