Deleting rows in a SQL database can sometimes be a nerve-racking experience. What if you’re deleting the wrong rows? Or what if the business later tells you they want their data back?
Fortunately SQL Server provides us with an easy way to essentially “back up” any rows affected by a DELETE operation to a table.
This article looks at using the OUTPUT ... INTO clause to save a copy of deleted rows to another table.
If you’re getting an error that reads something like “Violation of PRIMARY KEY constraint ‘PK_CatId’. Cannot insert duplicate key in object ‘dbo.Cats’. The duplicate key value is (1)” in SQL Server, it’s because you’re trying to insert a duplicate value into a primary key column.
A primary key cannot contain duplicate values.
To fix this issue, you’ll need to change the value you’re trying to insert into the primary key column.
The good thing about having IDENTITY columns in SQL Server is that they automatically generate a unique value for every row that’s inserted into a table. This saves us from having to insert our own unique values, and I’m not going to complain about that.
But obvious question you might ask; What if I need to know the value that was generated for each column I just inserted?
Fortunately, the solution may be easier than you think!
If you’re getting SQL Server error 137 that goes something like “Must declare the scalar variable…“, it’s probably because you’re referring to a variable that hasn’t been declared.
If the variable has been declared, it’s possible you’re referring to it incorrectly in the code.
When we use a variable in SQL Server, we must declare the variable first.
To fix this issue, declare the variable. Also be sure to use the right syntax when using it.
Any time you prepare to delete data from a database, you will probably wonder whether or not to log the deleted data somewhere… just in case.
One way to do this is to construct a SELECT statement to identify the rows you want to delete, and then use it to copy those rows into a separate log table, before doing the actual delete. If you’re using SQL Server, another option is to use the OUTPUT clause. This article discusses the later.
There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.
For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).
The query below can help if you need to find the name of a primary key’s index for a given table.
If you’re getting an error that reads “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server, it appears that you’re using the asterisk wildcard (*) to select all columns when trying to define a schema-bound object (like a schema-bound view).
As the error message states, this is not allowed.
To fix this issue, explicitly name each column.
Sometimes when working with SQL Server, we need to get a quick list of indexes on a given table.
Here are three queries that return all indexes on a given table in SQL Server.
Disabling constraints in SQL Server can be useful for various operations, such as bulk data loading or certain maintenance tasks. But not all constraints are created equal. The method we use to disable a constraint, and whether that’s even possible, depends on the type of constraint.
In this article, we’ll explore how to disable different types of constraints and consider the potential impacts.
If you’re trying to use a common table expression (CTE) that produces XML in SQL Server, but you’re getting error 4121, which reads something like “Cannot find either column “EmployeeData” or the user-defined function or aggregate “EmployeeData.query”, or the name is ambiguous” it might be that you’re trying to query the XML in the outer query, but your CTE isn’t outputting the XML as an actual xml type.
The FOR XML result must be an xml type in order to process the result on the server.
To fix this issue, make sure the CTE outputs the result using the xml data type.