How to create a dynamic query/CTE to be recursive based on number

Question

I am trying to automate a DB creation script where I receive the following table as an input:

INSERT INTO @FilePath (Directory,Type,NumberOfFiles)
VALUES ('E:\','DATA','2'),
       ('G:\','DATA','2'),
       ('F:\','LOG','1')

Where Directory is the directory where the files should be created
Type is the type of file to be created
NumberOfFiles is the number of files to be created.

For example the script should return for ('E:','DATA','2') two rows in the dynamic script:

 ( NAME = N''' + @DB_NAME + @FILE_NUMBER +', FILENAME = N'''+ @DataPath + @DB_NAME + '_Data\' + @DB_NAME + @FILE_NUMBER + ' , SIZE = 2097152KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB ),
 ( NAME = N''' + @DB_NAME + @FILE_NUMBER +', FILENAME = N'''+ @DataPath + @DB_NAME + '_Data\' + @DB_NAME + @FILE_NUMBER +' , SIZE = 2097152KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB )

Using the same technique as the question posted [just before yours](https://stackoverflow.com/questions/68166641/generate-data-from-1-to-specific-value) — SMor, Jun 28 '21 at 16:46

score 0 · Answer 1 · answered Jun 28 '21 at 17:01

I'd probably do something like this:

DECLARE @FilePath as TABLE(Directory SYSNAME, Type VARCHAR(8), NumberOfFiles TINYINT);

INSERT INTO @FilePath (Directory,Type,NumberOfFiles)
VALUES ('E:\','DATA','2'),
       ('G:\','DATA','2'),
       ('F:\','LOG','1')

DECLARE @DB_NAME as SYSNAME = 'Foo';

WITH 
   T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) X(N))
,  NUMS(N) AS (SELECT TOP (8) ROW_NUMBER() OVER (ORDER BY @@Version) AS N FROM T)
SELECT '( NAME = N''' + @DB_NAME + CAST(N.N as SYSNAME) +', FILENAME = N'''+ F.Directory + @DB_NAME + '_Data\' + @DB_NAME + CAST(N.N as SYSNAME) + ''' , SIZE = 2097152KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB ),'
FROM @FilePath F
JOIN NUMS N     ON N.N <= F.NumberOfFiles

Of course, you'd have to fit this into the context of however you are generating and executing the rest of your dynamic SQL. I think you'll need to take the file type into account somewhere in there as well.

How to create a dynamic query/CTE to be recursive based on number

1 Answers1