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.

These statements aren't being executed in parallel, so you might want to revise your title. — J. Polfer
– J. Polfer, Commented Jul 30, 2010 at 16:09

Fosco · Accepted Answer · 2010-07-30 16:08:13Z

24

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

answered Jul 30, 2010 at 16:08

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter McIntyre Over a year ago

while simple, this isn't always an option, see docs: "GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor." learn.microsoft.com/en-us/sql/t-sql/language-elements/…

Jeffrey L Whitledge · Accepted Answer · 2010-07-30 16:20:54Z

8

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.

answered Jul 30, 2010 at 16:20

Jeffrey L Whitledge

59.7k9 gold badges76 silver badges100 bronze badges

Comments

David Espart · Accepted Answer · 2010-07-30 16:09:38Z

2

You have to separate the statements with the GO keyword:

sql query
GO

another sql query
GO

and so on

answered Jul 30, 2010 at 16:09

David Espart

11.8k7 gold badges41 silver badges50 bronze badges

Comments

Mitch Wheat · Accepted Answer · 2010-07-30 16:08:34Z

1

By placing a GO between statements (to create separate batches of statements)

answered Jul 30, 2010 at 16:08

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Collectives™ on Stack Overflow

SQL - create database and tables in one script

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related