SQL Server Database Project in Visual Studio - how to structure for new database

Question

I want to create a SQL Server database project where a new database is created. Here is the structure of my project :

SQL Server Database Project Structure

Basically, I want 4 tables, so I have added 4 .sql scripts for them.

CREATE TABLE [dbo].[MovieActors]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [ActorID] INT NOT NULL,
    [MovieID] INT NOT NULL
)

CREATE TABLE [dbo].[Actors]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NOT NULL, 
    [Bio] NVARCHAR(MAX) NULL, 
    [Sex] NCHAR(10) NOT NULL, 
    [DOB] DATE NULL
)

CREATE TABLE [dbo].[Movies]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NOT NULL, 
    [Year of Release] SMALLINT NULL, 
    [PLOT] NVARCHAR(MAX) NULL, 
    [Poster] NVARCHAR(MAX) NULL,
    [ProducerId] int FOREIGN KEY REFERENCES Producers(Id)
)

CREATE TABLE [dbo].[Producers]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NOT NULL, 
    [Bio] NVARCHAR(MAX) NULL, 
    [Sex] NCHAR(10) NULL, 
    [DOB] DATE NULL
)

And I have also added pre-deployment (for deleting existing data) and post-deployment scripts (for seeding initial data).

Pre-Deployment:

DROP TABLE [dbo].MovieActors
DROP TABLE [dbo].Movies
DROP TABLE dbo.Actors
DROP TABLE dbo.Producers

Post-Deployment:

INSERT INTO dbo.Actors (Id, Name, Sex)
VALUES (1, 'Actor1', 'MALE')

INSERT INTO dbo.Producers (Id, Name, Sex)
VALUES (1, 'Producer1', 'MALE')

INSERT INTO dbo.Movies (Id, Name, ProducerId)
VALUES (1, 'Movie1', 1)

INSERT INTO dbo.MovieActors (Id, MovieID, ActorID)
VALUES (1, 1, 1)

1. But everytime I am building the project it does not get deployed - only showing build succeeded
2. Moreover, where is the database creation script.
3. When I right clicked on publish and chose option to generate script , only pre and post deployment scripts were in the main script. -

Right Click Project -> Publish option

This is the script that gets generated -

/*
Deployment script for MoviesDatabase

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "MoviesDatabase"
:setvar DefaultFilePrefix "MoviesDatabase"
:setvar DefaultDataPath "C:\Users\viiye\AppData\Local\Microsoft\VisualStudio\SSDT"
:setvar DefaultLogPath "C:\Users\viiye\AppData\Local\Microsoft\VisualStudio\SSDT"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
/*
 Pre-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.   
 Use SQLCMD syntax to include a file in the pre-deployment script.          
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the pre-deployment script.        
 Example:      :setvar TableName MyTable                            
               SELECT * FROM [$(TableName)]                 
--------------------------------------------------------------------------------------
*/
Drop table [dbo].MovieActors
Drop table [dbo].Movies
Drop table dbo.Actors
Drop table dbo.Producers
GO

GO
/*
Post-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.       
 Use SQLCMD syntax to include a file in the post-deployment script.         
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the post-deployment script.       
 Example:      :setvar TableName MyTable                            
               SELECT * FROM [$(TableName)]                 
--------------------------------------------------------------------------------------
*/
INSERT INTO dbo.Actors (Id,Name,Sex)
values (1,'Actor1','MALE')

INSERT INTO dbo.Producers (Id,Name,Sex)
values (1,'Producer1','MALE')

INSERT INTO dbo.Movies (Id,Name,ProducerId)
values (1,'Movie1',1)

Insert into dbo.MovieActors (Id,MovieID,ActorID)
values (1,1,1)

GO

GO
PRINT N'Update complete.';


GO

As can be seen , the create tables script are not there in this script which is used for publish .Why? and is this the correct way to go about requirement where I need to create tables from scratch?

Answer 1

This is are the simplified steps how the project is published:

1. Project is built and DACPAC file is the output of this step
2. DACPAC is compared with the destination database and the publish script is generated
3. Publish script is executed against the destination database

The problem in your case is that at the time when the 2nd step is executed the tables existed at the destination database, so their creation was not included to the publish script. Pre deployment script is executed at the step 3!

Basically what you need to do is not to drop tables. Just TRUNCATE them in pre script and populate in the post. Or simply use MERGE statements in the post script. You can use generate-sql-merge procedure that would generate MERGE statement for needed table.