create table

How to Safely Drop and Recreate a Table If It Exists in SQL Server

by

Sometimes you need to rebuild a table from scratch. This will usually involve dropping the table and recreating it. Maybe it’s for a schema update, or maybe it’s for reloading data in a staging environment. Whatever the reason, SQL Server provides a straightforward way to handle this.

While we can certainly go right ahead and drop the table, what if it doesn’t actually exist? In this case we’ll get an error. That is unless we take measures to prevent such an error. Such measures will involve checking for the existence of the table before attempting to drop it.

Read more

Using DROP TABLE IF EXISTS When Working with Temporary Tables in SQL Server

by

In SQL Server, when working with temporary tables, it’s common to ensure a temp table with the same name doesn’t already exist before creating it. This prevents any unwanted errors. SQL Server doesn’t support CREATE TABLE IF NOT EXISTS like some other DBMSs do, so you must explicitly check and drop the table beforehand.

Read more

5 Ways of Checking the Existence of Temporary Tables in SQL Server

by

When working with temporary tables in SQL Server, one of the most common tasks is checking whether the table already exists before creating it. This prevents errors in the event the table already exists, and ensures your scripts run smoothly regardless of previous executions.

In this article, we’ll explore five different approaches to checking for temporary table existence in SQL Server.

Read more

Understanding WITHOUT ROWID Tables in SQLite

by

One feature that sets SQLite apart from most other RDBMSs is the concept of WITHOUT ROWID tables. This is an optimization feature designed to improve performance and reduce storage space for certain use cases.

This article explores what WITHOUT ROWID tables are, how they work, their benefits, and when to use them.

Read more

An Introduction to Strict Tables in SQLite

by

SQLite is widely known for its simplicity, flexibility, and lightweight architecture. One feature that sets it apart from most other SQL databases is its dynamic typing system, which allows columns in a table to store data of any type, regardless of their declared type.

While some developers welcome this departure from the traditional SQL approach, others find it extremely problematic, due to its non-enforcement of data types, which could potentially lead to data integrity issues.

Read more

Fix “Column names in each table must be unique…” in SQL Server (Error 2705)

by

If you’re getting an error that reads something like “Column names in each table must be unique. Column name ‘c1’ in table ‘t1’ is specified more than once” in SQL Server, it appears that you’re trying to create a table with duplicate column names, or you’re trying to add a column to a table that already has a column of that name.

Column names must be unique within each table.

Read more

CREATE TABLE IF NOT EXISTS Equivalent in SQL Server

by

In SQL, we can use the CREATE TABLE IF NOT EXISTS statement to create a table only if it doesn’t exist. The benefit of doing this is that we won’t get an error if there’s already a table with the same name.

But SQL Server doesn’t support this syntax – at least not in the current version of SQL Server at the time of writing (SQL Server 2022) .

So with SQL Server, we need to do a bit of extra work.

Read more

Oracle Adds Support for IF EXISTS and IF NOT EXISTS Syntax Modifiers

by

Many RDBMSs implement IF EXISTS and IF NOT EXISTS syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE and DROP TABLE statements, to name just a couple.

These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).

Read more