SQL - GO in IF Condition

Question

I need to make a SQL-Script, which can be executed multiple times on the same DB, to check if a table already exists. If yes don't do anything, if no create the table and insert some data. The problem is, that I can't use 'GO' inside the BEGIN and END tags.

What I need (Code doesn't work):

    IF (OBJECT_ID('dbo.Report', 'U') IS NULL)
    BEGIN
        CREATE TABLE [dbo].[Report](
            [ReportID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Report_ReportID_1]  DEFAULT (newid()),
            [Name] [nvarchar](100) NULL,
            [Description] [ntext] NULL,
            [Query] [ntext] NULL,
            CONSTRAINT [PK_Reporting] PRIMARY KEY CLUSTERED 
            (
                [ReportID] ASC
            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
            ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
        )
        GO   

        INSERT [dbo].[Report] ([ReportID], [Name], [Description], [Query]) VALUES (N'1', N'04. People and groups', N'People and groups', N'select * from V_REPORT04USERGROUPS order by Login')
        GO

    END
    GO

Can someone tell me the most clean way to do this? Thanks!

And you don't need GO in this script at all. Also as zedfoxus noted add ; to your statements. — Cetin Basoz, Oct 21 '15 at 13:05
Thanks for the feedback, seems like I missed that in the whole SQL-Script stuff — Helvetios, Oct 21 '15 at 13:14
You should avoid the ntext datatype. It has been deprecated since sql 2005. You should instead use nvarchar(max). — Sean Lange, Oct 21 '15 at 14:43

zedfoxus · Accepted Answer · 2015-10-21T13:06:18.173

2

Try this and make sure you provide a datatype for reportID

IF (OBJECT_ID('dbo.Report1', 'U') IS NULL)
BEGIN
    CREATE TABLE [dbo].[Report1](
        [ReportID] int NULL,
        [Name] [nvarchar](100) NULL,
        [Description] [ntext] NULL,
        [Query] [ntext] NULL
    );

    INSERT INTO [dbo].[Report1] ([ReportID], [Name], [Description], [Query]) VALUES (N'1', N'04. People and groups', N'People and groups', N'select * from V_REPORT04USERGROUPS order by Login');

END
GO

edited Oct 21 '15 at 13:06

answered Oct 21 '15 at 13:03

zedfoxus

35,121
5
64
63

@vkp Oh yes, `INSERT INTO` - caught the missing int but forgot the `INTO`. Nice eye. Thank you. – zedfoxus Oct 21 '15 at 13:07
I shortened my create table (to make the code a bit more clear to show on here) and accidentally deleted the Datatype for the ReportID. Thanks your solution seems to work pretty well – Helvetios Oct 21 '15 at 13:07

score 1 · Answer 2 · answered Oct 21 '15 at 13:08

1

IF (OBJECT_ID('dbo.Report', 'U') IS NULL)
BEGIN
    CREATE TABLE [dbo].[Report](
        [ReportID] NULL,
        [Name] [nvarchar](100) NULL,
        [Description] [ntext] NULL,
        [Query] [ntext] NULL
    );


    INSERT [dbo].[Report] 
    ([ReportID], [Name], [Description], [Query]) 
    VALUES 
    (N'1', N'04. People and groups', N'People and groups', 
     N'select * from V_REPORT04USERGROUPS order by Login');

END

All columns accepting NULL and no Primary Key?

answered Oct 21 '15 at 13:08

Cetin Basoz

22,495
3
31
39

Accidentally deleted too much of the 'Create table' statement to keep the code clear to show it off on here. I edited to post with the original create statement. Thanks for the feedback! – Helvetios Oct 21 '15 at 13:12

SQL - GO in IF Condition

2 Answers2