0

I have to create a folder structure. To do so, I have written a long query which seems to me very ugly: 

declare @KundeVorname nvarchar(50) 
declare @KundeNachname nvarchar(50) 
declare @path nvarchar(255)

select @KundeVorname=a.firstname0 from ADDRESSES a where a.ID=10 
select @KundeNachname=a.lastname0 from ADDRESSES a where a.ID=10

select @path='MD \\OUK-SRV-01\Arbeitsablage\'+@KundeNachname+'_'+@KundeVorname+'\01'+'" "'+'Legitimation\01'+'" "'+'Ausweis'

EXEC xp_cmdshell @path 

select @path='MD \\OUK-SRV-01\Arbeitsablage\'+@KundeNachname+'_'+@KundeVorname+'\01'+'" "'+'Legitimation\02'+'" "'+'Aufenthaltsdokumente'

EXEC xp_cmdshell @path 

select @path='MD \\OUK-SRV-01\Arbeitsablage\'+@KundeNachname+'_'+@KundeVorname+'\02'+'" "'+'OPTIN' 

EXEC xp_cmdshell @path

..... and so on

There should be a way to create a loop, so that I can execute xp_cmdshell only one time. That might fasten my query. Could you please give me a hint, which changes I need?

Thanks in advance!

anastasiiia
  • 189
  • 2
  • 12
  • 2
    Do you mean a [CURSOR](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017)? Using SQL Server to create all your directories doesn't seem ideal though. It might actually be more performant to use SQL Server to create a Powershell Script (using `STRING_AGG` or the `FOR XML PATH` and `STUFF), which you can paste into the Powershell ISE and execute. – Thom A Mar 05 '19 at 10:50
  • 1
    Some automation can be done with a `CURSOR` or `WHILE`. The dataset with commands to execute can be defined using T-SQL and then executed line by line using a CURSOR. It will help hardly with performance, but your code will be, imho, more maintainable because logic is reused in a loop – Alexander Volok Mar 05 '19 at 10:50

0 Answers0