mercredi 21 octobre 2015

SQL - GO in IF Condition

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!

Aucun commentaire:

Enregistrer un commentaire