If sql table exists create

Question

This question has been answered many times before but when I use the code, I get an error:

Incorrect syntax near ')'.

IF NOT EXISTS (SELECT 'X'
               FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_NAME = 'table_name'
                 AND TABLE_SCHEMA = 'schema')
BEGIN
    // create..
END

My code

IF NOT EXISTS (SELECT *
               FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_NAME = 'control_forecastscenario'
                 AND TABLE_SCHEMA = 'rvfc')
BEGIN
    CREATE TABLE [rvfc].[control_forecastscenario]
    (
        [Scenario] [varchar](255) NULL,
        [Active] [varchar](255) NULL
    ) ON [PRIMARY]

The error is on Line 4

AND TABLE_SCHEMA = 'rvfc')

Looks like you need to finish off your BEGIN block with and END statement. Otherwise the statement ran fine for me. — Rex Henderson, Apr 17 '21 at 00:20
And of course you need to create your rvfc schema beforehand — Rex Henderson, Apr 17 '21 at 00:21
@RexHenderson thank you quick question do you think this method or incorporating a trigger would be more sufficient — Yigan32, Apr 17 '21 at 00:30
I'm not clear what your goals are, but stay away from triggers if at all possible. — Rex Henderson, Apr 17 '21 at 00:38
When OP clarifies his goals, I'll provide a better answer. Thanks — Rex Henderson, Apr 17 '21 at 01:11
Your syntax as you have posted it here is correct for MS SQL, this is not reproducable as it is displayed, are you executing this from management studio or from a script or a code routine? — Chris Schaller, Apr 17 '21 at 01:18
So @Yigan32 is this still a problem? The error suggests that only part of your query is executing, but with the information provided, we cannot replicate the issue. `IF NOT EXISTS ... BEGIN... CREATE TABLE ... END` is a very common reusable pattern for DDL scripts, it looks like you're doing the right thing. — Chris Schaller, Apr 18 '21 at 06:58
@ChrisSchaller the only issue I still have is the ON PRIMARY I must keep it within the code but im getting an incorrect syntax on Primary — Yigan32, Apr 19 '21 at 00:15
Do you have the `END` statement after the `ON PRIMARY`? The error means that something immediately after it is incorrect. But again, this is very strange, there is something you're not posting because what you have posted does work in a vanilla environment — Chris Schaller, Apr 19 '21 at 01:04

score 1 · Answer 1 · answered Apr 17 '21 at 01:48

As a single query, the syntax you have posted works perfectly. You didn't include the END to your BEGIN block in the post, but we know that is not the issue because the error message would have had a different line number in it.

The following single expression will work:
(I like to indent the BEGIN but it makes no difference to the execution)

IF NOT EXISTS (SELECT *
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_NAME = 'control_forecastscenario'
    AND TABLE_SCHEMA = 'rvfc')
BEGIN
    CREATE TABLE [rvfc].[control_forecastscenario](
        [Scenario] [varchar](255) NULL,
        [Active] [varchar](255) NULL
    )
END

Also note that I have omitted the ON [PRIMARY] as that is the default file group and if you are not managing multiple file groups then it is better not to include references to them in your code base.

The only way to recreate your error is by trying to execute this as separate batched calls. This might happen if your business logic is executing a script in chunks or line by line, or if you have somehow built the query in a way that a batch terminator like GO or ; has been injected into the script.

The following will create your error.

IF NOT EXISTS (SELECT *
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_NAME = 'control_forecastscenario'
    AND TABLE_SCHEMA = 'rvfc')

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

The same error will occur if a simi-colon in injected into the original script.

IF NOT EXISTS (SELECT *
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_NAME = 'control_forecastscenario'
    AND TABLE_SCHEMA = 'rvfc')
;
BEGIN
CREATE TABLE [rvfc].[control_forecastscenario](
    [Scenario] [varchar](255) NULL,
    [Active] [varchar](255) NULL
    )
END

In addressing some other comments, the existence of the schema rvfc is not in question here, nor are other common syntax issues because they would raise different error messages like this:

Msg 2760, Level 16, State 1, Line 7
The specified schema name "rvfc" either does not exist or you do not have permission to use it.

If you have executed this statement, omitting the END you would see a different line number, but the same message:

IF NOT EXISTS (SELECT *
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_NAME = 'control_forecastscenario'
    AND TABLE_SCHEMA = 'rvfc') BEGIN
CREATE TABLE [rvfc].[control_forecastscenario](
    [Scenario] [varchar](255) NULL,
    [Active] [varchar](255) NULL
    )

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ')'.

The message is the same because the BEGIN block requires closure with an END which makes the entire BEGIN block invalid. So ) was the last valid character in the script, so that is the last place SQL can report the error.

Please post your code that is executing the script for further assistance, otherwise this post should be closed as not reproducable.

If sql table exists create

1 Answers1