Sunday, 14 January 2024

Generating repeated data into variable in SQL Server in T-SQL

Let's see how we can create repeated data into variable of SQL Server in T-SQL. Use the REPLICATE function to create repeated data like this:


DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE('.', 10)
PRINT @myVariable
PRINT len(@myVariable)





In case you want to set the variable to data which is longer than 8000 characters, you must convert the argument to NVARCHAR(MAX).


DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE(CONVERT(NVARCHAR(MAX),'.'), 1024*1024*2)
PRINT len(@myVariable)


Creating random content is also easy in T-SQL:

DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE(CONVERT(NVARCHAR(MAX),REPLACE(NEWID(),'-', '')), 4)
PRINT len(@myVariable)
PRINT @myVariable

NEWID() creates a new guid, and we strip away the '-' letter, giving 32 chars which we replicate above four times. Since we were below 8000 chars, we chould have skipped using convert to nvarchar(max).
Share this article on LinkedIn.

No comments:

Post a Comment