ssdt project in visual studio 2013 - Deploy alterscript for not empty Tables

Question

I hoop you can Help me. I use the Skriptgeneration in the Schemacomparetool of Visual Studio 2013 SDDT Project. My Problem is I need to Update a Table that's not empty.

The Schemacompartool make this:

/*
The type for column Bundesland in table [dbo].[Arbeitsfreiertag] is currently  VARCHAR (50) NULL but is being changed to  BIGINT NULL. Data loss could occur.
*/

IF EXISTS (select top 1 1 from [dbo].[Arbeitsfreiertag])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO

...
PRINT N'Altering [dbo].[Arbeitsfreiertag]...';


GO
ALTER TABLE [dbo].[Arbeitsfreiertag] ALTER COLUMN [Bundesland] BIGINT NULL;

I need also the change skript like Managment Studio Designer change:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Arbeitsfreiertag
    (
    ID bigint NOT NULL,
    Bundesland bigint NULL,
    Name varchar(50) NOT NULL,
    ArbeitsfreiertagTyp bigint NOT NULL,
    Beginn datetime NOT NULL,
    Beschreibung varchar(200) NULL,
    Ende datetime NULL,
    Land bigint NOT NULL,
    Wiederholend int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Arbeitsfreiertag SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Arbeitsfreiertag)
     EXEC('INSERT INTO dbo.Tmp_Arbeitsfreiertag (ID, Bundesland, Name, ArbeitsfreiertagTyp, Beginn, Beschreibung, Ende, Land, Wiederholend)
        SELECT ID, CONVERT(bigint, Bundesland), Name, ArbeitsfreiertagTyp, Beginn, Beschreibung, Ende, Land, Wiederholend FROM dbo.Arbeitsfreiertag WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Arbeitsfreiertag
GO
EXECUTE sp_rename N'dbo.Tmp_Arbeitsfreiertag', N'Arbeitsfreiertag', 'OBJECT' 
GO
ALTER TABLE dbo.Arbeitsfreiertag ADD CONSTRAINT
    PK_Arbeitsfreiertag PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

I need a option to set this in ShemaCompartool.

I hope you understand my question.

Thanks Steffen

scar80 · Accepted Answer · 2015-04-23T06:23:14.170

I think the problem is that your primary data type is NVARCHAR and you want to change it to BIGINT. In general scenario it will fail as you can't convert any string into integer.

What you need to do is to manually create a pre-deployment and post-deployment scripts and handle data conversion there. The script should look something like this:

--This is pre-deployment script
-- data backup into temp table
PRINT 'Backup data from dbo.Arbeitsfreiertag'
SELECT * INTO tmp_Arbeitsfreiertag from dbo.Arbeitsfreiertag
GO

PRINT 'TRUNCATE TABLE dbo.Arbeitsfreiertag'
TRUNCATE TABLE dbo.Arbeitsfreiertag
GO

--This is post-deployment script
--copy data from temp table into main table
PRINT 'Copy data from temp table tmp_Arbeitsfreiertag into main table'

INSERT INTO dbo.Arbeitsfreiertag (ID, Bundesland, Name, ArbeitsfreiertagTyp, Beginn, Beschreibung, Ende, Land, Wiederholend)
SELECT ID, CONVERT(bigint, Bundesland), Name, ArbeitsfreiertagTyp, Beginn, Beschreibung, Ende, Land, Wiederholend 
FROM tmp_Arbeitsfreiertag WITH (HOLDLOCK TABLOCKX)
GO

IF ((SELECT COUNT(1) FROM tmp_Arbeitsfreiertag) = (SELECT COUNT(1) FROM Arbeitsfreiertag))
BEGIN
  PRINT 'DROP TABLE tmp_Arbeitsfreiertag'
  DROP TABLE tmp_Arbeitsfreiertag
END
GO

ssdt project in visual studio 2013 - Deploy alterscript for not empty Tables

1 Answers1