Rename list of files from SQL Server 2012

Question

I have a table which has 1000+ records with file name, file path and new file name. I want to rename existing files from FileName to NewFileName. Is there any possibility to rename these files from SQL Server 2012.

I am able to do this for one file but I have a problem with doing this for multiple files.

DECLARE @cmd varchar(1000)
SET @cmd = 'rename C:\Test\A.txt A_new.txt'
EXEC master..xp_cmdshell @cmd 

Answer 1

You can do this from SQL Server if you want. The solution depends on where the information to rename is stored:

---

1. The information is stored in a TABLE variable or a Temporary Table

   • Insert the rename statements into a global temporary table
   • Export the global temporary table using BCP to a batch file
   • Execute the batch file
   • Delete the batch file
   • Drop the global temporary table

---

2. The information is stored in a regular table

   • Export the rename statements using BCP with a query to a batch file
   • Execute the batch file
   • Delete the batch file

---

Example script for case 1 (TABLE variables, Temporary Tables):

SET NOCOUNT ON;

DECLARE @fn TABLE(
    file_path VARCHAR(1024),
    new_file_name VARCHAR(256)
);

INSERT INTO @fn(file_path,new_file_name)VALUES
    ('C:\Temp\A.txt','A_new.txt'),
    ('C:\Temp\B.txt','B_new.txt'),
    ('C:\Temp\C.txt','C_new.txt');

SELECT
    'rename "'+file_path+'" "'+new_file_name+'"' AS stmt
INTO
    ##cmd_output
FROM
    @fn;

DECLARE @cmd VARCHAR(8000);
SET @cmd='BCP "SELECT stmt FROM ##cmd_output" QUERYOUT "C:\Temp\rename_all.bat" -c -T -S ' + @@SERVERNAME;
EXEC master..xp_cmdshell @cmd;
EXEC master..xp_cmdshell 'C:\Temp\rename_all.bat';
EXEC master..xp_cmdshell 'DEL C:\Temp\rename_all.bat';

DROP TABLE ##cmd_output;

---

Example script for case 2 (regular tables):

CREATE TABLE file_renames(
    file_path VARCHAR(1024),
    new_file_name VARCHAR(256)
);
GO

INSERT INTO file_renames(file_path,new_file_name)VALUES
    ('C:\Temp\A.txt','A_new.txt'),
    ('C:\Temp\B.txt','B_new.txt'),
    ('C:\Temp\C.txt','C_new.txt');

DECLARE @cmd VARCHAR(8000);
SET @cmd='BCP "SELECT ''rename ""''+file_path+''"" ""''+new_file_name+''""'' AS stmt FROM file_renames" QUERYOUT "C:\Temp\rename_all.bat" -c -T -S ' + @@SERVERNAME + ' -d ' + DB_NAME();
EXEC master..xp_cmdshell @cmd;
EXEC master..xp_cmdshell 'C:\Temp\rename_all.bat';
EXEC master..xp_cmdshell 'DEL C:\Temp\rename_all.bat';