Template script for updating sanitized messages with sample text from a file for SQL Server

update-messages-with-sample-text-from-file.sql

sp_configure 'show advanced options',1  
reconfigure;
GO

sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure;
GO


CREATE TABLE SampleText (
	[text] VARCHAR(MAX)
);
GO

BULK INSERT SampleText
	FROM 'C:\Users\viveret steele\Downloads\samples.txt'
	WITH
	(
		FIELDTERMINATOR ='$$$$$$$',
		ROWTERMINATOR ='\n'
	);
GO

sp_configure 'Ad Hoc Distributed Queries',0
reconfigure;
GO

sp_configure 'show advanced options',0 
reconfigure;
GO

--declare @subjectSampleCount int = (SELECT COUNT(*) FROM [ViveretsData].dbo.SampleText WHERE LEN([text]) < 30)
--declare @bodySampleCount int = (SELECT COUNT(*) FROM [ViveretsData].dbo.SampleText WHERE LEN([text]) >= 30)

declare @amount int = 1000;
declare @start int = @amount * 1;

WITH SubjectSamples AS (
	SELECT TOP 1494 *, ROW_NUMBER() OVER (ORDER BY NEWID()) [rand]
		FROM [ViveretsData].dbo.SampleText WHERE LEN([text]) < 30 ORDER BY [rand]
	),
	BodySamples AS (
	SELECT TOP 13068 *, ROW_NUMBER() OVER (ORDER BY NEWID()) [rand]
		FROM [ViveretsData].dbo.SampleText WHERE LEN([text]) >= 30 ORDER BY [rand]
	)
UPDATE m
	SET m.[Body] = bs.[text],
	    m.[Subject] = ss.[text]
	FROM Messages m
		INNER JOIN SubjectSamples ss ON ss.[rand] = m.Id % 1494 + 1
		INNER JOIN BodySamples bs ON bs.[rand] = m.Id % 13068 + 1
	WHERE m.Id BETWEEN @start AND @start + @amount;

GO