How to pass a variable in a folder path using TSQL

Question

How to pass a variable in a folder path using TSQL

josetur12

Default port

Points: 1424
More actions
August 23, 2022 at 4:23 am

#4079507
Hello, I need help to rename a folder name from 20220822, which is the current date to anything, "newdirname", for example.
"C:\Test\20220822\"
I would like to use a sql script.
Or, if anyone knows how to get this done using PowerShell, I will appreciate feedback.
Thanks
- This topic was modified 1 year, 8 months ago by josetur12.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27779 More actions · Answer 1

In your server you can enable xp_cmdShell

exec sp_configure 'xp_cmdShell'

To view the current setting for this option. You can run CMD commands within your script with this (be aware of security vulnerabilities however). Google this setting to learn about it.

Are you trying to do this within a t-sql script ? I ask because you also threw powershell in there.

----------------------------------------------------

josetur12 Default port Points: 1424 More actions · Answer 2

Hi, this is what I am trying to do,

DECLARE @todays AS VARCHAR(20)

SELECT @todays = format(cast(getdate() as date),'yyyyMMdd')

EXEC xp_cmdshell

'copy C:\Test\@todays\ *.* C:\Newtest\';

But, I can figure out the syntax to pass the @todays to replace the date.

Thanks

This reply was modified 1 year, 8 months ago by josetur12.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

josetur12 wrote:

Hi, this is what I am trying to do,
DECLARE @todays AS VARCHAR(20) SELECT @todays = format(cast(getdate() as date),'yyyyMMdd')
EXEC xp_cmdshell 'copy C:\Test\@todays\ *.* C:\Newtest\';
But, I can figure out the syntax to pass the @todays to replace the date.
Thanks

Even for something so small, I strongly recommend getting out of the habit of using FORMAT for anything. It's like practicing the piano... unless you're practicing a comedy act based on the piano, you don't want to practice hitting the wrong keys. Here's an article that explains the beginning of why that's my recommendation...

https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

josetur12 Default port Points: 1424 More actions · Answer 4

Jeff, format date is not the issue.

If you are willing to waste your time, do it on the real problem, the variable is the issue.

DECLARE @todays AS VARCHAR(20) SELECT @todays = format(cast(getdate() as date),'yyyyMMdd')

EXEC xp_cmdshell 'copy C:\Test\@todays\ *.* C:\Newtest\';

But, I can figure out the syntax to pass the @todays to replace the date.

MMartin1 One Orange Chip Points: 27779 More actions · Answer 5

Something like the following ?

declare @cmdStr as varchar(100);
declare @today AS CHAR(8) 

set @today = '20220805'; /* Or however you want to set this */
set @cmdStr = 'copy C:\Test\'+ @today+ '\ *.* C:\Newtest\';

Select @cmdStr;


EXEC xp_cmdshell @cmdStr;

This reply was modified 1 year, 8 months ago by MMartin1.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27779 More actions · Answer 6

I might as well add, if this is supposed to be routing you could create a JOB that runs powershell to do the move. With powershell you can actually go recursivley (meaning through subfolders) within C:\Test\ and move these contents to the final destination. The Job could be scheduled daily, weekly, however. The name of the folder within Test\ would not matter . Or you could declare a variable to only search within that folder.

Powershell is worth learning if you've never tried it. So many resources on line to get you started.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

josetur12 wrote:

Jeff, format date is not the issue.
If you are willing to waste your time, do it on the real problem, the variable is the issue.
DECLARE @todays AS VARCHAR(20) SELECT @todays = format(cast(getdate() as date),'yyyyMMdd')
EXEC xp_cmdshell 'copy C:\Test\@todays\ *.* C:\Newtest\';
But, I can figure out the syntax to pass the @todays to replace the date.

Uh huh... what do You use for a million rows? If I were a betting man, I'd say you probably use format. 😉

It's not a waste of time doing it the right way all the time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

josetur12 Default port Points: 1424 More actions · Answer 8

josetur12

Default port

Points: 1424

August 24, 2022 at 10:07 pm

#4080297

Martin, your solution worked.

Thanks,

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

josetur12 wrote:

Martin, your solution worked.
Thanks,

So I can learn a bit, which one of Martin's replies does that cover?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)