Sql-server – Data Migration and altering tables in one SSDT deployment script

deploymentmigrationsql serversql-server-2012ssdt

I'm using SSDT and MS SQL Server 2012.
And I need to handle next cases in one deployment script:
--1
CREATE TABLE Table1 (Col1 INT)
--2
INSERT INTO Table1(Col1)
SELECT SomeCol FROM SomeTable
--3
ALTER TABLE dbo.SomeTable DROP COLUMN SomeCol
--4
INSERT INTO AnotherTable(AnotherCol)
VALUES(1)

What can I see now in my deployment script:

--Pre-deployment part
--some insert. Will fail because it select/insert from/to not created objects

--Deployment part
CREATE TABLE Table1 (Col1 INT)
GO
ALTER TABLE dbo.SomeTable DROP COLUMN SomeCol
GO

--Post-deployment part
--some insert. Will fail because it select/insert from/to not created objects

How do I need to configure post/pre-deployment scripts?
Is it possible to do it in SSDT automatically during deployment?

Appreciate for any proposals.

Best Answer

I'm unsure if this is in the version of SSDT/Visual Studio you're using but as far as I know, in the Database Project type - you can add Pre/Post Deployment scripts by simply adding right clicking your project and adding a Script > Pre/Post-Deployment script.

When you publish this, and generate the script, you'll see the pre and post deployment scripts in the SQLCMD syntax.

In terms of doing it automatically, I don't think what you're asking is possible. Personally, I found generating DACPACs to be quite useful for deployment. Develop your database and publish to a DACPAC and you can almost automate your deployment. However, your specific example requires some knowledge of your implementation, so I can't think of a way it could programmatically know this. Apologies if I'm not reading you correctly.

Related Question