Creates SQL objects for Slack analysis and adjusts the usernames in the message text column

Slack.sql

--Create table to store posts
CREATE TABLE [dbo].[Posts](
	[Username] [nvarchar](255) NULL,
	[Type] [nvarchar](255) NULL,
	[Subtype] [nvarchar](255) NULL,
	[TS] [nvarchar](255) NULL,
	[Channel] [nvarchar](255) NOT NULL,
	[MessageText] [nvarchar](max) NULL,
	[MessageDate] [datetime2](7) NULL,
	[PostID] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED 
(
	[PostID] ASC
)
)
GO

--Create table to store users for more meaningful text analysis
CREATE TABLE Users(UserID NVARCHAR(50), UserName NVARCHAR(100), RealName NVARCHAR(255))
GO

--Import the users separately
--expects the users.json in the given folder
DECLARE @JSON NVARCHAR(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET(BULK 'C:\SlackExport\users.json', SINGLE_CLOB) AS oro
    
INSERT dbo.Users ( UserID, UserName, RealName )
SELECT DISTINCT id, name, real_name
FROM OPENJSON(@JSON)
WITH (id nvarchar(50), name nvarchar(100), real_name nvarchar(255)) AS oj


--Create a Full-Text index on the MessageText table to allow for faster update to replace user id w/ user name
CREATE FULLTEXT CATALOG [Messages]
WITH ACCENT_SENSITIVITY = ON
GO
CREATE FULLTEXT INDEX ON [dbo].[Posts] (
	        MessageText Language 1033 
) 
KEY INDEX PK_Posts
	        ON [Messages]
WITH CHANGE_TRACKING AUTO
GO

/*
  Populate the Posts table using the Powershell script
*/



--Run the following after populating the Posts table to replace the Slack UserID w/ the User Name
DECLARE UserCursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT UserId, UserName
FROM dbo.Users AS u

DECLARE @UserID NVARCHAR(50), @UserName NVARCHAR(100)

OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @UserID, @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE Posts SET MessageText = REPLACE(MessageText, @UserID, @UserName)
  WHERE CONTAINS(MessageText, @UserID)
  FETCH NEXT FROM UserCursor INTO @UserID, @UserName
END
CLOSE UserCursor
DEALLOCATE UserCursor