jabbr-migrate.sql

BEGIN TRANSACTION

-- Remove existing constraints on old table
ALTER TABLE [dbo].[ChatMessages] DROP CONSTRAINT [FK_ChatMessages_ChatRooms_Room_Key]
GO

ALTER TABLE [dbo].[ChatMessages] DROP CONSTRAINT [FK_ChatMessages_ChatUsers_User_Key]
GO

-- Create new table
CREATE TABLE [dbo].[ChatMessages_new](
	[Key] [int] IDENTITY(1,1) NOT NULL,
	[Content] [nvarchar](max) NULL,
	[Id] [nvarchar](max) NULL,
	[When] [datetimeoffset](7) NOT NULL,
	[Room_Key] [int] NULL,
	[User_Key] [int] NULL,
	[HtmlEncoded] [bit] NOT NULL DEFAULT (1),
	[HtmlContent] [nvarchar](max) NULL,
	PRIMARY KEY NONCLUSTERED ( [Key] ASC )
)
GO

-- Add the clustered index
CREATE CLUSTERED INDEX IX_ChatMessages_When ON [dbo].[ChatMessages_new] ([When])
GO

-- Copy the data over a row at a time
DECLARE @key int
SELECT TOP 1 @key = [Key] FROM [dbo].[ChatMessages] ORDER BY [Key]
WHILE @@ROWCOUNT > 0 BEGIN
    INSERT [dbo].[ChatMessages_new] SELECT [Content], [Id], [When], [Room_Key], [User_Key], [HtmlEncoded], [HtmlContent] FROM [dbo].[ChatMessages] WHERE [Key] = @key  -- one row
    SELECT TOP 1 @key = [Key] FROM [dbo].[ChatMessages] WHERE [Key] > @key ORDER BY [Key]
END

-- Drop the old table
DROP TABLE [dbo].[ChatMessages]
GO

-- Rename table
EXEC sp_rename 'ChatMessages_new', 'ChatMessages'

-- Add constraints to new table
ALTER TABLE [dbo].[ChatMessages] WITH NOCHECK ADD CONSTRAINT [FK_ChatMessages_ChatRooms_Room_Key] FOREIGN KEY([Room_Key])
REFERENCES [dbo].[ChatRooms] ([Key])
GO

ALTER TABLE [dbo].[ChatMessages] CHECK CONSTRAINT [FK_ChatMessages_ChatRooms_Room_Key]
GO

ALTER TABLE [dbo].[ChatMessages] WITH NOCHECK ADD  CONSTRAINT [FK_ChatMessages_ChatUsers_User_Key] FOREIGN KEY([User_Key])
REFERENCES [dbo].[ChatUsers] ([Key])
GO

ALTER TABLE [dbo].[ChatMessages] CHECK CONSTRAINT [FK_ChatMessages_ChatUsers_User_Key]
GO

COMMIT TRANSACTION