.SQL File Format – Ending statements

The semicolon

In all the dialects a semicolon ends a statement, unless the dialect’s other rules say otherwise. It does not end a statement when it is inside a string, a quoted name or a comment.

INSERT INTO note VALUES ('one; two');  -- this ; is the end of the statement

DELIMITER, in MySQL

The body of a MySQL routine or trigger has semicolons in it. So that the whole body is sent as one statement, the mysql client has a command that changes what ends a statement. It is not SQL and never reaches the server.

DELIMITER ;;
CREATE TRIGGER fruit_ai AFTER INSERT ON fruit FOR EACH ROW
BEGIN
  UPDATE stats SET n = n + 1;
END ;;
DELIMITER ;

The command takes up its own line. mysqldump uses ;; for triggers and routines.

GO, in SQL Server

GO ends a batch: a group of statements sent to the server together. It is not a Transact-SQL statement. It is read by sqlcmd, osql and SQL Server Management Studio, and is never sent to the server. No statement can share its line, but a comment can. A number after it runs the batch that many times.

CREATE TABLE [dbo].[fruit] ([id] [int] NOT NULL)
GO
SET IDENTITY_INSERT [dbo].[fruit] ON
INSERT [dbo].[fruit] ([id]) VALUES (1)
INSERT [dbo].[fruit] ([id]) VALUES (2)
SET IDENTITY_INSERT [dbo].[fruit] OFF
GO

Semicolons are optional in Transact-SQL, so inside a batch a new statement often just begins on the next line. A CREATE PROCEDURE, FUNCTION, TRIGGER or VIEW must be the only statement in its batch, so it runs to the next GO, and its own semicolons do not end it.

Bodies, in PostgreSQL and SQLite

PostgreSQL function bodies are usually dollar-quoted strings, so their semicolons are already inside a string. The newer BEGIN ATOMIC … END body has semicolons inside it and is read as a block.

SQLite has no DELIMITER. A trigger’s body is between BEGIN and END, with a semicolon after each statement in it, and the shell knows that the statement continues to the matching END.

CREATE TRIGGER fruit_ai AFTER INSERT ON fruit BEGIN
  UPDATE stats SET n = n + 1;
END;

COPY, in PostgreSQL

A COPY … FROM stdin statement ends at its semicolon, and then its data follows, ending at a line of \.. See COPY.