tldr: -- Create Comment Categories table IF NOT EXISTS (SELECT 1 FROM…
- User
tldr: -- Create Comment Categories table IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StudentCommentCategories') BEGIN CREATE TABLE StudentCommentCategories ( Id INT IDENTITY(1,1) PRIMARY KEY, Text NVARCHAR(255) NOT NULL, Active BIT NOT NULL DEFAULT 1 ); END;
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'General comment') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('General comment', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Employment info') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Employment info', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Pay & agreements') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Pay & agreements', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Feedback & Evaluation') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Feedback & Evaluation', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Screening & interview notes') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Screening & interview notes', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Sickness & absence') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Sickness & absence', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Status update') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Status update', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Accidents, health & safety') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Accidents, health & safety', 1);
-- Add Category to Comment IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CommentsOnStudent' AND COLUMN_NAME = 'StudentCommentCategoriesId') BEGIN ALTER TABLE CommentsOnStudent ADD StudentCommentCategoriesId INT NOT NULL DEFAULT 1; END;
IF NOT EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_CommentsOnStudent_StudentCommentCategories' ) BEGIN ALTER TABLE CommentsOnStudent ADD CONSTRAINT FK_CommentsOnStudent_StudentCommentCategories FOREIGN KEY (StudentCommentCategoriesId) REFERENCES StudentCommentCategories(Id); END
IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE name = 'IX_CommentsOnStudent_StudentCommentCategoriesId' AND object_id = OBJECT_ID('CommentsOnStudent') ) BEGIN CREATE INDEX IX_CommentsOnStudent_StudentCommentCategoriesId ON CommentsOnStudent (StudentCommentCategoriesId); END
Assistant
This SQL script performs several database operations:
- Create StudentCommentCategories Table:
- Creates a new table if it doesn't already exist
- Table has columns: Id (auto-incrementing primary key), Text (category name), Active (boolean)
- Populate Default Categories: Inserts 8 default comment categories if they don't already exist:
- General comment
- Employment info
- Pay & agreements
- Feedback & Evaluation
- Screening & interview notes
- Sickness & absence
- Status update
- Accidents, health & safety
- Modify CommentsOnStudent Table:
- Adds a new column
StudentCommentCategoriesId - Sets a default value of 1 for this column
- Creates a foreign key constraint linking to StudentCommentCategories table
- Creates an index on the new column for performance optimization
The script uses conditional checks to prevent errors if objects already exist, making it safe to run multiple times. It ensures data integrity by establishing a relationship between comments and their categories.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.