Ian

Fix “Invalid column name ‘$action'” When Using the OUTPUT Clause in SQL Server (Error 207)

by

If you’re getting an error that reads “Invalid column name ‘$action’” in SQL Server, it could be that you’re using the $action argument of the OUTPUT clause for an operation that doesn’t support this argument.

Or it could be that the operation does support the argument, but you’re referencing it with the wrong syntax.

The $action argument can only be used with MERGE operations, and it must be done correctly.

To fix this issue, be sure to only use the $action argument in MERGE operations and to reference it with the correct syntax.

Read more

SQL Server MERGE Statement: Beginner’s Guide with Examples

by

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.

Read more

SQL Joins with the USING Clause: How It Compares to the ON Clause

by

Perhaps one of the lesser-known clauses when it comes to SQL joins is the USING clause. While the more widely-used ON clause allows us to explicitly specify join conditions, the USING clause simplifies the syntax when the join is based on columns with the same name in both tables.

In this article, we’ll dive into the USING clause, compare it to the ON clause, and look at examples that illustrate the difference.

Read more

Fix “Column, parameter, or variable #1: Cannot find data type” in SQL Server (Error 2715)

by

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.

Read more

A Deep Dive into PostgreSQL’s TRUNCATE Statement

by

Most relational database management systems support the TRUNCATE statement, and PostgreSQL is no exception.

That said, PostgreSQL has a few differences in the way its TRUNCATE statement works when compared to many other RDBMSs.

In this article, we’ll explore the various features of PostgreSQL’s implementation of the TRUNCATE statement, along with examples to demonstrate.

Read more

A Comparison of 6 SQL Rank Functions

by

SQL rank functions can be handy things to have when analysing data. Most major RDBMSs implement a similar bunch of ranking functions, usually with the same names. These rank functions allow us to assign rankings to rows based on specific criteria.

In this article, we’ll look at six commonly used SQL ranking functions, and observe how they differ. We’ll throw them all together into a single query and see their results side by side.

The rank functions in question are: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), and CUME_DIST().

Read more

Fix “A MERGE statement must be terminated by a semi-colon (;)” in SQL Server (Error 10713)

by

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.

Read more

Updating Composite Values in PostgreSQL

by

When we have a column with composite data in PostgreSQL, we can access each field by using dot notation. This is true whether we select the data or update it. This means that we can use dot notation to update individual fields within a composite column.

And if we need to update all fields at once, we can use the ROW constructor.

Read more

Fix “Cannot insert explicit value for identity column in table” in SQL Server (Error 544)

by

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).

Read more