Sql - Create Database and Tables in One Script

SQL - create database and tables in one script

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

Create SQL script that create database and tables

In SQL Server Management Studio you can right click on the database you want to replicate, and select "Script Database as" to have the tool create the appropriate SQL file to replicate that database on another server. You can repeat this process for each table you want to create, and then merge the files into a single SQL file. Don't forget to add a using statement after you create your Database but prior to any table creation.

In more recent versions of SQL Server you can get this in one file in SSMS.

1. Right click a database.
2. Tasks
3. Generate Scripts

This will launch a wizard where you can script the entire database or just portions. There does not appear to be a T-SQL way of doing this.

Create database and table in a single .sql script

This worked for me:

CREATE DATABASE mydb;
GO
USE mydb;
GO
CREATE TABLE mydb.dbo.Customers
    (
      ID INT ,
      FirstName VARCHAR(255) ,
      LastName VARCHAR(255),
    );

Create multiple tables using single .sql script file

You need to separate the create table scripts with / or end the command with ;, Try like this,

CREATE TABLE ACCOUNT_DETAILS_TB ( CUSTOMER_ID VARCHAR2(20) NOT NULL , ACCOUNT_ID VARCHAR2(20) NOT NULL )
/
CREATE TABLE ADDRESS_DETAILS_TB ( ACCOUNT_ID VARCHAR2(20) NOT NULL , ADDRESS_ID VARCHAR2(20) NOT NULL )
/

OR

CREATE TABLE ACCOUNT_DETAILS_TB ( CUSTOMER_ID VARCHAR2(20) NOT NULL , ACCOUNT_ID VARCHAR2(20) NOT NULL );

CREATE TABLE ADDRESS_DETAILS_TB ( ACCOUNT_ID VARCHAR2(20) NOT NULL , ADDRESS_ID VARCHAR2(20) NOT NULL );

Create database, tables etc in one script postgresql

Replace the T-SQL batch terminator GO with the PostgreSQL batch terminator ;.

GO is not supported in postgreSQL

• Microsoft SQL Server to PostgreSQL Migration by Ian Harding

you need to connect on the database using \. eg,

 CREATE DATABASE testdatabase; 
 \c testdatabase 
 CREATE TABLE testtable (testcolumn int); 

• PostgreSQL for MySQL users

Is it possible to create a single sql script to both create a database and to populate it with tables?

With a bash script like this:

#!/bin/bash
psqluser="U"   # username
psqlpass="U"   # password
psqldb="U"   # db

sudo printf "CREATE USER $psqluser WITH PASSWORD '$psqlpass';\nCREATE DATABASE $psqldb WITH OWNER $psqluser;\nGRANT ALL PRIVILEGES ON database $psqldb TO $psqluser;" > createdb.sql

sudo -u postgres psql -f createdb.sql

echo "Populating inserting.sql"
sudo -u postgres psql -d $psqldb -f inserting.sql

where inserting.sql is something like:

CREATE TABLE T (
    id serial,
    description text
);

ALTER TABLE T OWNER TO U;

INSERT INTO T(description) VALUES ('description 1');
INSERT INTO T(description) VALUES ('description 2');

ALTER TABLE ONLY T ADD CONSTRAINT id_pkey PRIMARY KEY (id);

---

Related Topics