I'm working on a school assignment that wants me to insert some new values into a database table and then print a message based on whether or not the INSERT was successful.
The question goes like this:
Write a script that attempts to insert a new category named “Guitars” into the Categories table. If the insert is successful, the script should display this message: SUCCESS: Record was inserted.
If the update is unsuccessful, the script should display a message something like this: FAILURE: Record was not inserted. Error 2627: Violation of UNIQUE KEY constraint 'UQ_Categori_8517B2E0A87CE853'. Cannot insert duplicate key in object 'dbo.Categories'. The duplicate key value is (Guitars).
Currently, this Categories table consists of 2 columns: CategoryID and Category name. It's populated with the values
1 Guitars
2 Basses
3 Drums
4 Keyboards
Obviously the Guitars category that the question wants you to insert is already there, so I'm guessing the whole point of the question is to get it to print the error message. The logic of the question seems fairly straightforward; insert the Guitars category into the table. If the insert was successful, print such-and-such. If it was unsuccessful, print so-and-so. I'm just not sure about the syntax. Here's the SQL code I've got so far:
USE MyGuitarShop;
INSERT INTO Categories (CategoryID, CategoryName)
VALUES (5, 'Guitars')
IF ( ) --insert is successful
PRINT 'SUCCESS: Record was inserted'
ELSE --if insert is unsuccessful
PRINT 'FAILURE: Record was not inserted.'
PRINT 'Error 2627: Violation of UNIQUE KEY constraint 'UQ__Categori__8517B2E0A87CE853'.'
PRINT 'Cannot insert duplicate key in object 'dbo.Categories'. The duplicate key value is (Guitars).'
I feel like there'd be some sort of boolean equation in that IF statement (IF INSERT = success, IF success = TRUE, etc.) but I'm just not sure how to write it. Am I on the right track?
EDIT: I should mention I'm using SQL Server 2012
There are two basic syntax of INSERT INTO statement is as follows: INSERT INTO TABLE_NAME (column1, column2, column3,... columnN)] VALUES (value1, value2, value3,... valueN);
You can find INSERT time using fn_dblog() function looking at [Begin Time] column of the corresponding begin transaction (with INSERT name) in the result set.
I would use try/catch myself
begin try
insert query
print message
end try
begin catch
print message
end catch
You should be able to take it from here.
USE MyGuitarShop
GO
BEGIN TRY
-- Insert the data
INSERT INTO Categories (CategoryName)
VALUES ('Guitars')
PRINT 'SUCCESS: Record was inserted.'
END TRY
BEGIN CATCH
PRINT 'FAILURE: Record was not inserted.';
PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER(), 1) + ': '+ ERROR_MESSAGE()
END CATCH
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With