Create Database and Table Conditionally

Question

I'm trying to write a small script to create a database if it doesn't exist, and create a table for that database if the table doesn't exist. What I have is this:

IF (db_id('db') is null) BEGIN
    print 'Must create the database!';
    CREATE DATABASE db;
END

USE db;

IF (object_id('test_table', 'U') is null) BEGIN
    print 'Must create the table!';
    CREATE TABLE test_table (
        id int
    );
END

I'm getting a strange error with this:

Database 'db' does not exist. Make sure that the name is entered correctly.

I'm guessing that it's parsing the script before running it and finding that 'db' doesn't exist, so it can't use it.

There must be a solution to this. Any help is appreciated.

SOLVED!

I realised 5 minutes after posting that the GO keyword solves the problem. Here is the fixed code:

IF (db_id('db') is null) BEGIN
    print 'Must create the database!'
    CREATE DATABASE db;
END

GO
USE db

IF (object_id('test_table', 'U') is null) BEGIN
    print 'Must create the table!';
    CREATE TABLE test_table (
        id int
    );
END

Sorry for wasting everyone's time.

MS-SQL. I've discovered that the GO keyword makes a huge difference. I think it's solved the problem. — Mr_Bean, Oct 08 '13 at 20:09
Yep, you should use go between some statements in sql server, i always forget which ones so i just spam GO :P — Andrei Dvoynos, Oct 08 '13 at 20:11
You should answer your own question, and accept the answer. It will help others who have the same problem. — John Saunders, Oct 08 '13 at 20:42
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Oct 08 '13 at 20:43

score 2 · Accepted Answer · answered Oct 08 '13 at 20:26

SQL statements are parsed as one batch unless you break them apart. In SQL Server, you can use GO to do this. In both MySQL and SQL Server, you can use BEGIN and END.

If you want to commit the separate blocks to the database in different instances you can use BEGIN TRANS / COMMIT TRANS and START TRANSACTION / COMMIT for SQL Server and MySQL, respectively.

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

Something along the lines of Check if table exists in SQL Server would probably work (With a slight change)

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

edited May 23 '17 at 10:33

Community

1
1

answered Oct 08 '13 at 20:11

jama

325
1
6

score 0 · Answer 3 · answered Oct 08 '13 at 20:06

0

I might suggest using the built-in SQL syntax -

CREATE DATABASE name IF NOT EXISTS;

And subsequently

CREATE TABLE name(definition) IF NOT EXISTS;

answered Oct 08 '13 at 20:06

DigitalJedi805

1,486
4
16
41

Create Database and Table Conditionally

3 Answers3