SQLServer recipe: Create file paths based on rows in a table

Use case: given  the products in the Product table (all examples use the AdventureWorks database), for those products whose reorder point is <= 5, for each product in that set create a root folder, and under each products root folder, create an Invoices folder, and an Orders folder. CLR could be used to actually create the folders, but as CLR is banned in many companies for security reasons, we can just use this output to create a static file of e.g. PowerShell commands. This example just gives the folders, rather than the full command line:

;with fileSystem as (
 select 'c:\ProductFiles\' as rootFolder
)
, product as (
select p.ProductID, f.rootFolder + p.Name as productFileLocation
 from [adventureworks2014].[production].[product] p
 cross apply fileSystem f
 where p.ReorderPoint <= 5
)
select ProductID, productFileLocation from product
union all
select ProductID, productFileLocation + '\Orders' from product
union all
select ProductID, productFileLocation + '\Invoices' from product
order by ProductID

ProductsSQL01

Sample of the result:

ProductsSQL02 ProductsSQL03

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s