SQL - create database and tables in one script

Question

Sorry if already asked, but I can't find anything on this.

I am moving something over from MySQL to SQL Server I want to have a .sql file create a database and tables within the database. After working out syntax kinks I have gotten the files to work (almost).

If I run

IF db_id('dbname') IS NULL 
    CREATE DATABASE dbname

it works fine, and if I run

CREATE TABLE dbname.dbo.TABLE1 (
);
...
CREATE TABLE dbname.dbo.TABLEN (
);

it also works fine. But, if I run them in the same file I get this error

Database 'dbname' does not exist

Right now, the CREATE TABLE statements are not within the IF statement, which I would like, but I also cannot seem to find the syntax for that. ( { } does not work?)

So my big question is, how do I ensure a particular command in a .sql file is completed before another in SQL Server?

My second question is, how do I include multiple instructions within an IF clause?

To be clear, I have been running this into sqlcmd.

Answer 1

Put a GO command between queries.

IF db_id('dbname') IS NULL 
    CREATE DATABASE dbname

GO

CREATE TABLE dbname.dbo.TABLE1 ( 
); 

CREATE TABLE dbname.dbo.TABLEN ( 
); 

As for putting the table statements in the IF, you wouldn't be able to because of the GO command. You could create additional IF statements afterwards, to check for each tables pre-existence.

The syntax for a block if is:

IF condition
BEGIN
   ....
   ....
END

Answer 2

Between creating the database and creating the tables you will need a USE statement.

USE dbname

This way the tables will be created in the correct place, without having to specify the DB name on everything.

Also, GO and BEGIN...END like everyone else is saying.

Answer 3

You have to separate the statements with the GO keyword:

sql query
GO

another sql query
GO

and so on