Creating several databases and inserting multiple values into them. SQL

Question

I want to simultaneously create several tables in a database and insert values into them. I am using SQL Server Management Studio. That's my code:

CREATE DATABASE Movies
CREATE TABLE Directors (
    Id int PRIMARY KEY IDENTITY,
    DirectorName nvarchar(50) NOT NULL, 
    Notes nvarchar(1000)
    );
INSERT INTO Directors (DirectorName, Notes)
VALUES ('John', 'some notes'),
('John', 'some notes'),
('John', 'some notes'),
('John', 'some notes'),
('John', 'some notes');
CREATE TABLE Genres (
    Id int PRIMARY KEY IDENTITY,
    GenreName nvarchar(50) NOT NULL, 
    Notes nvarchar(1000)
    );
INSERT INTO Genres (GenreName, Notes)
VALUES ('drama', 'some notes'),
('drama', 'some notes'),
('drama', 'some notes'),
('drama', 'some notes'),
('drama', 'some notes');
CREATE TABLE Categories (
    Id int PRIMARY KEY IDENTITY,
    CategoryName nvarchar(50) NOT NULL, 
    Notes nvarchar(1000)
    );
INSERT INTO Categories (CategoryName, Notes)
VALUES ('Documentary', 'drama', 'some notes'),
('Documentary', 'drama', 'some notes'),
('Documentary', 'drama', 'some notes'),
('Documentary', 'drama', 'some notes'),
('Documentary', 'drama', 'some notes');
CREATE TABLE Movies (
    Id int PRIMARY KEY IDENTITY,
    Title nvarchar(50) NOT NULL, 
    DirectorId int NOT NULL,
    CopyrightYear date,
    Length int,
    GenreId int,
    CategoryId int,
    Rating int,
    Notes nvarchar(1000)
    );
INSERT INTO Movies (
    Title, 
    DirectorId,
    CopyrightYear,
    Length,
    GenreId,
    CategoryId,
    Rating,
    Notes )
VALUES ('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes');

And that's the error I get:

CREATE DATABASE permission denied in database 'master'.
An explicit value for the identity column in table 'Categories' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I would be glad if someone explained the specifics of creating multiple tables and inserting values in all of them in the same statement.

Which dbms are you using? You're having a product specific problem. — jarlh, Jan 23 '17 at 08:35
You have the columns `CategoryName`and `Notes` but try to insert 3 values: `VALUES('Documentary', 'drama', 'some notes')` — Filburt, Jan 23 '17 at 08:35

score 0 · Accepted Answer · answered Jan 23 '17 at 08:52

You need to select the database after creating it by using the USE command. e.g.

    CREATE DATABASE Movies

USE Movies -- You need this line to use the newly created database

    CREATE TABLE Directors (
        Id int PRIMARY KEY IDENTITY,
        DirectorName nvarchar(50) NOT NULL, 
        Notes nvarchar(1000)
        );
    INSERT INTO Directors (DirectorName, Notes)
    VALUES ('John', 'some notes'),
    ('John', 'some notes'),
    ('John', 'some notes'),
    ('John', 'some notes'),
    ('John', 'some notes');
    CREATE TABLE Genres (
        Id int PRIMARY KEY IDENTITY,
        GenreName nvarchar(50) NOT NULL, 
        Notes nvarchar(1000)
        );
    INSERT INTO Genres (GenreName, Notes)
    VALUES ('drama', 'some notes'),
    ('drama', 'some notes'),
    ('drama', 'some notes'),
    ('drama', 'some notes'),
    ('drama', 'some notes');
    CREATE TABLE Categories (
        Id int PRIMARY KEY IDENTITY,
        CategoryName nvarchar(50) NOT NULL, 
        Notes nvarchar(1000)
        );
    INSERT INTO Categories (CategoryName, Notes)
    VALUES ('Documentary', 'drama', 'some notes'),
    ('Documentary', 'drama', 'some notes'),
    ('Documentary', 'drama', 'some notes'),
    ('Documentary', 'drama', 'some notes'),
    ('Documentary', 'drama', 'some notes');
    CREATE TABLE Movies (
        Id int PRIMARY KEY IDENTITY,
        Title nvarchar(50) NOT NULL, 
        DirectorId int NOT NULL,
        CopyrightYear date,
        Length int,
        GenreId int,
        CategoryId int,
        Rating int,
        Notes nvarchar(1000)
        );
    INSERT INTO Movies (
        Title, 
        DirectorId,
        CopyrightYear,
        Length,
        GenreId,
        CategoryId,
        Rating,
        Notes )
    VALUES ('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
    ('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
    ('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
    ('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes'),
    ('Dumbo', 1, '1923-07-09', 180, 1, 1, 10, 'some notes');

That's it. Thank you! – Martin Dimitrov Jan 23 '17 at 08:56 — Martin Dimitrov, Jan 23 '17 at 08:56

Creating several databases and inserting multiple values into them. SQL

1 Answers1