0

I am trying to make few tables in the database.
& the tool I`m using is Azure Data Studio.

Command that I`m using it:-

CREATE OR ALTER TABLE TABLE_NAME
(
    Date         datetime,
    Sequence         nvarchar(8),
    Code     nvarchar(3),
)
    GO

Error that I am getting:- enter image description here

What I wanted to do is, create a table if not exist or if it does exist then alter it. I`m unable to understand why its failing.

We already have something available on Stackover Flow itself Incorrect Syntax near the keyword 'OR' in CREATE OR ALTER PROCEDURE

We have create and alter for external tables here.

Why this isnt working?

Lakshay Rohilla
  • 1,654
  • 6
  • 9
  • 30
  • 1
    You need to remove conflicting tags `MySQL` <> `SQL Server`. In `MySQL` there is no `CREATE OR ALTER TABLE` . In `SQL Server` , possibly in other RDBMS too, `The CREATE OR ALTER statement works with specific types of database objects such as stored procedures, functions, triggers and views` – Ergest Basha Apr 22 '22 at 08:50
  • Confusing, the `create or alter` suggests it is not sql-server, but the `go` does suggests it is sql-server. So, which is it ? – GuidoG Apr 22 '22 at 09:54

1 Answers1

1

I found out there is no CREATE OR ALTER syntax for tables. It works only for views, triggers, functions and stored procedures.

The corresponding MSSQL Tiger Team blog post explains the situation:-

CREATE OR ALTER can be used in programmability objects such as:

  • STORED PROCEDURES (including natively compiled)
  • FUNCTIONS (Transact-SQL, including natively compiled)
  • TRIGGERS
  • VIEWS

But cannot be used in: Objects that require storage (tables, indexes and indexed views)

  • CLR user-defined functions
  • Deprecated programmability objects (RULE and DEFAULT)
  • Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE - SCHEMA). On these objects, the syntax for CREATE and ALTER is very different from a syntax and usability perspective.

Instead, we can create table if it doesn't exist (e.g like here) or explicitly drop it first:

DROP TABLE IF EXISTS FOO;
CREATE TABLE FOO (...)

OR

DROP TABLE IF EXISTS FOO;
    GO
CREATE TABLE FOO (...)

Both will work fine.

I hope this will help the community.

Lakshay Rohilla
  • 1,654
  • 6
  • 9
  • 30
  • I would not recommend dropping and recreating tables, this will give you problems when there are foreign keys involved. You know how to check if the table exists, so use that to either create the table or alter the table – GuidoG Apr 22 '22 at 09:56
  • Example link for that is already provided - https://stackoverflow.com/questions/6520999/create-table-if-not-exists-equivalent-in-sql-server/6521016#6521016 – Lakshay Rohilla Apr 22 '22 at 10:18
  • Yes but have you read it thoroughly ? And have you read and understood my comment ? The check if a table exists if fine, but dropping an existing table and recreate it in stead of altering the table, that is what you should avoid, that is what I am saying – GuidoG Apr 22 '22 at 10:52
  • @GuidoG I got your point, I`ll dig more into it, and then update the answers. Thanks a lot for sharing your knowledge. – Lakshay Rohilla Apr 22 '22 at 18:41