SQL Server Database Project

I must admit most (if not all) my SQL Server interaction takes place in SQL Server Management Studio, but I wanted to create a new database project using the Visual Studio database tools, so I thought I’d give this a go…

Getting Started

I always like to start such posts off with a set of steps for getting the basics up and running, so let’s continue with that way of doing things.

  • Create a new SQL Server | SQL Server Database Project (mine’s called cddb)
  • Select the project in solution explorer and if need be, open the project properties/settings and set the Target platform etc.
  • Right mouse click on the project and select Add | Table…
  • Name the table artist
  • Repeat the last two steps but name the table album

So at this point we have a database project and two tables/sql scripts with nothing much in them.

We’re going to create some very basic tables, as this post isn’t mean’t to be too focused on data but more using these tools.

So for artist.sql we should have

CREATE TABLE [dbo].[artist]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
[Name] NVARCHAR(50) NOT NULL
)

and for album.sql we should have

CREATE TABLE [dbo].[album]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
[Title] NVARCHAR(50) NOT NULL, 
[ArtistId] INT NOT NULL, 
CONSTRAINT [FK_album_Toartist] 
  FOREIGN KEY (ArtistId) 
  REFERENCES [artist]([Id])
)

Deploy/Publish your database

At this point, let’s actually publish our database to an instance of SQL Server or SQL Server Express.

Right mouse click on the project and select Publish, you should have the Database name supplied as cddb and the script as cddb.sql. Click the Edit button and enter the connect details for the instance of SQL Server. Finally click on the generate script button if you wish to create DB script and then run this yourself or click the Publish button to automatically publish your tables to the SQL Sever instance.

In the Data Tools Operations view you’ll see the process of publishing and creating the database scripts. Once successfully completed you should now have the cddb database running in SQL Server.

Let’s add some data

In a continuous integration and/or continuous deployment scenario, it’s useful to recreate our database from scripts, so generating the script instead of publishing to the database obviously helps in this, but it’s also useful to generate some data. Ofcourse it could be we’re populating the data from another instance of the DB but for this example we’re going to add some data via an SQL script.

Right mouse click on the database project and select Add | Script… We’re going to create a post-deployment script. As the name suggests this should be run after the DB is generated. I’ve named my script populate.sql, you’ll notice in the Visual Studio properties window the Advanced | Build Action will show PostDeploy.

We’re going to use the T-SQL Merge statement to create our test data, this script is as follows

SET IDENTITY_INSERT artist ON
GO

MERGE artist AS target
USING (VALUES
   (1, N'Alice Cooper'),
   (2, N'Van Halen'),
   (3, N'Deep Purple')
)
AS source (Id, Name)
ON target.Id = source.Id
WHEN MATCHED THEN
   UPDATE SET Name = source.Name
WHEN NOT MATCHED BY target THEN
   INSERT (Id, Name)
   VALUES (Id, Name)
WHEN NOT MATCHED BY source THEN
   DELETE;
GO

SET IDENTITY_INSERT artist OFF
GO

SET IDENTITY_INSERT album ON
GO

MERGE album AS target
USING (VALUES
   (1, N'Lace and Whiskey', 1),
   (2, N'I', 1),
   (3, N'III', 1),
   (4, N'Burn', 2)
)
AS source (Id, Title, ArtistId)
ON target.Id = source.Id
WHEN MATCHED THEN
   UPDATE SET Title = source.Title, ArtistId = source.ArtistId
WHEN NOT MATCHED BY target THEN
   INSERT (Id, Title, ArtistId)
   VALUES (Id, Title, ArtistId)
WHEN NOT MATCHED BY source THEN
   DELETE;
GO

SET IDENTITY_INSERT album OFF
GO

Ofcourse the above would be somewhat unwieldy if we’re populating hundreds of hundreds or MB of data entries.

Populating data from CSV

One possible solution for populating a larger number of records might be to use one or more CSV files to contain our seed data. So let’s assume we have the following files

artists.csv

1,Alice Cooper
2,Van Halen
3,Deep Purple

and albums.csv

1,Lace and Whiskey,1
2,I,1
3,III,1
4,Burn,2

we could now replace our post deployment code with the following

BULK INSERT artist
   FROM 'artists.csv'
   WITH
   (
   FIRSTROW=1,
   FIELDTERMINATOR=',',
   ROWTERMINATOR='\n',
   TABLOCK
   )

GO

BULK INSERT album
   FROM 'albums.csv'
   WITH
   (
   FIRSTROW=1,
   FIELDTERMINATOR=',',
   ROWTERMINATOR='\n',
   TABLOCK
   )

GO

Importing data using SQL Server Management Studio

Whilst this doesn’t fit in with the context of this post, i.e. it’s not automated. You could ofcourse create the database and use SQL Server Management Studio’s Import task to import data into your database.

Simply select the database you want to import data into, right mouse click on this and select Tasks | Import Data and work through the wizard to import your data from a variety of sources.