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

Why do you think SQL Server would be better suited to this task than say, a batch script? Is it because you have the new file names in a database already, and they do not follow an obvious pattern? — Bridge, Jan 21 '16 at 16:46
I already have names in the database and yes, new names doesn't follow any pattern. — Hemus San, Jan 21 '16 at 16:59
When all you have is a hammer, everything looks like a nail. Don't use `xp_cmdshell` for this, don't do it from within the database at all. Write a PowerShell script to query the database (to get the name translation) & perform the renames using `Move-Item`. — alroc, Jan 21 '16 at 18:48
Why don't you recommend to use xp_cmdshell? How to do that in PowerShell? — Hemus San, Jan 21 '16 at 20:29
He's saying that because [xp_cmdshell can be dangerous](http://security.stackexchange.com/q/2722). So yeah, there are times you really need that call to xp_cmdshell, but you should strive to avoid it. PowerShell is a good alternative but requires knowledge of .NET classes to query your database for the old filenames/new filenames. If you already do and know some PowerShell, go ahead and try it out. If you don't have the time or don't want to delve into .NET/PowerShell, consider my answer. — TT., Jan 21 '16 at 21:08

TT. · Accepted Answer · 2016-01-23T11:05:47.570

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

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

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';

Rename list of files from SQL Server 2012

1 Answers1