The MERGE statement in SQL Server allows us to perform INSERT, UPDATE, and DELETE operations in a single query. This makes it an efficient way to synchronize two tables, typically between a source and a target, based on a defined condition. Rather than running separate queries to handle insertions, updates, and deletions, we can combine all of these operations into one statement; the MERGE statement.
Tag: mssql
Fix “Column, parameter, or variable #1: Cannot find data type” in SQL Server (Error 2715)
If you’re getting SQL Server error 2715, which reads something like “Column, parameter, or variable #1: Cannot find data type SERIAL“, it appears that you’re trying to define a column to have a data type that’s not supported in SQL Server.
To fix this issue, be sure to use a supported data type for all columns.
Continue readingFix “A MERGE statement must be terminated by a semi-colon (;)” in SQL Server (Error 10713)
If you’re getting SQL Server error 10713 that reads “A MERGE statement must be terminated by a semi-colon (;)“, it’s because you’re running a MERGE statement without terminating it with a semi-colon.
The MERGE statement requires a semi-colon at the end of it in order to correctly terminate the statement.
To fix this issue, put a semi-colon at the end of your MERGE statement.
Return the Values that were Updated by an UPDATE Statement in SQL Server (Both Old and New Values)
When we run an UPDATE statement in SQL, we might not always be interested in which rows or values were updated. But there may be times when we need to examine this data, or log it somewhere like in a separate table.
This is where SQL Server’s OUTPUT clause can help tremendously.
Fix “Cannot insert explicit value for identity column in table” in SQL Server (Error 544)
If you’re getting an error that reads “An explicit value for the identity column in table ‘Dogs’ can only be specified when a column list is used and IDENTITY_INSERT is ON” in SQL Server, it appears that you’re trying to insert a value into an IDENTITY column.
Specifically, you’re trying to do that while the IDENTITY_INSERT option is set to OFF.
To fix this issue, either enable IDENTITY_INSERT before inserting the value, or omit the value from your list of values to insert (and let the IDENTITY column do it’s thing).
4 Ways to List All Indexes in a SQL Server Database
By default, SQL Server creates indexes automatically when we do things like create certain constraints. We also have the option of creating indexes separately for our own (usually performance related) reasons. Either way, there may be times where we need to check what indexes we have in our database.
In this article, we’ll explore four ways to retrieve information about all indexes in a SQL Server database.
Continue readingFix Error 1909 “Cannot use duplicate column names in index” in SQL Server
If you’re getting SQL Server error 1909 that reads something like “Cannot use duplicate column names in index. Column name ‘FirstName’ listed more than once“, it appears that you’re trying to create an index, but you’re listing the same column multiple times.
Each column can only be listed once in the index definition.
To fix this issue, make sure each column is listed only once.
Continue readingWhat Does “Schema-Bound” Mean in SQL Server?
In SQL Server, “schema-bound” refers to a specific feature that ensures a SQL object, such as a view or function, is tightly linked to the schema of the underlying tables or other database objects it references. When a view or function is schema-bound, the underlying tables or objects cannot be altered in a way that would break the view or function’s dependencies.
Continue readingHow to Log Data Changes with the SQL Server OUTPUT Clause
SQL Server has an OUTPUT clause that we can use any time we do an INSERT, UPDATE, DELETE, or MERGE operation. It allows us to retrieve information from modified rows during such operations. This can be especially useful for auditing, logging, or understanding the impact of database changes without needing an additional query.
Let’s look at how the OUTPUT clause can help us log the data changes in our database.
Using a CTE with a DELETE Statement in SQL Server to Remove Duplicates
Common Table Expressions (CTEs) are a nifty SQL feature that allow us to define a temporary result set, which can then be used in subsequent queries, including DELETE statements. In the context of removing duplicate data, a CTE can be helpful when we want to identify and remove only the duplicated rows, keeping one version of each unique record.
Let’s go through a step-by-step example of using a CTE with a DELETE statement to remove duplicate data.