CODEWITHSUNDEEP
t-sqlconcatenation
Java
Java

Reputation: 1292

How do I express wildcard value with CONCAT in T-SQL?

I have these file names:

WH_BEE_FULL_20241014_170853_1.bak
WH_BEE_FULL_20241014_170853_2.bak
WH_BEE_FULL_20241014_170853_3.bak
WH_BEE_FULL_20241014_170853_4.bak
WH_BEE_FULL_20241014_170853_5.bak
WH_BEE_FULL_20241014_170853_6.bak

I am trying to use a wildcard (similar to *) with concat in a part of T-SQL script where it points to to where files are located.

Basically, I am having an issue to express '170853'.

I am trying to just use a wildcard to ignore any different pattern that would occur.

Bottom is the whole T-SQL that I run to restore DB, but only the area that needs attention is where it starts with 'CONCAT'.

DECLARE @sql nvarchar(max);
DECLARE @maxInputFiles BIGINT = 6;

WITH L1 AS (
    SELECT *
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n)
),
L2 AS (
    SELECT 1 n
    FROM L1 AS a, L1 AS b
)
SELECT @sql = N'
RESTORE DATABASE [WH_BEE]
FROM ' +
  STRING_AGG(
    CONCAT('
DISK = N''F:\Test\WH_BEE_FULL',
      '_',
      convert(varchar(8),cast(GETDATE()-3 as date),112),
      '_',
      '%',
      '_',
      g.value,        
      '.bak'''
    ),
    ', '
  ) + N'
WITH REPLACE, FILE = 1, 
MOVE N''WH_Data1'' TO N''F:\data2022\WH_BEE.MDF'',  
MOVE N''WH_Data2'' TO N''F:\data2022\WH_BEE_1.NDF'',  
MOVE N''WH_Log'' TO N''G:\log2022\WH_BEE.LDF'', STATS = 5;
'
FROM (
    SELECT TOP(@maxInputFiles)
      ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS value
    FROM L2
) AS g;


EXEC master.sys.sp_executesql @sql;

Upvotes: 1

Views: 56

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5694

You can use xp_dirtree to get the list of files in a folder, then you can compose the T-SQL command to restore the database like this:

DECLARE @Folder VARCHAR(100)='F:\Test\'

DECLARE @FileNames TABLE (Name VARCHAR(100) PRIMARY KEY, Depth INT, IsFile BIT)
INSERT INTO @FileNames (Name, Depth, IsFile)
EXEC xp_DirTree @Folder,0,1

DECLARE @sql nvarchar(max);
DECLARE @maxInputFiles BIGINT = 6;

SELECT @sql = N'RESTORE DATABASE [WH_BEE] FROM 
' +
  STRING_AGG('DISK = N'''+@Folder+Name,', 
') + N'
WITH REPLACE, FILE = 1, 
MOVE N''WH_Data1'' TO N''F:\data2022\WH_BEE.MDF'',  
MOVE N''WH_Data2'' TO N''F:\data2022\WH_BEE_1.NDF'',  
MOVE N''WH_Log'' TO N''G:\log2022\WH_BEE.LDF'', STATS = 5;'
FROM @FileNames
WHERE Name LIKE 'WH_BEE_FULL_'+convert(varchar(8),cast(GETDATE()-3 as date),112)+'%.bak'

PRINT @sql;
EXEC master.sys.sp_executesql @sql;

See also the warning well written in https://dba.stackexchange.com/a/144281/29446.

Upvotes: 0

Related Questions