在数据表中存在数据行的情况下,有人知道从数据库中删除现有列的最佳方法吗。
我尝试过的方法似乎不能正常工作。我在数据库项目中包含了一个预部署脚本,其中执行了一些操作。
GO
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Mercury.dbo.Discounts' and COLUMN_NAME = 'ColumnToRemove')
BEGIN
ALTER TABLE Database.dbo.Table1 Drop Column ColumnToRemove
END
GO
然后在创建表的脚本中,我从“创建表脚本”中删除了有问题的列。
执行dacpac后,我收到以下反馈:
Initializing deployment (Start)
*** The column [dbo].[Table1].[ColumnToRemove] is being dropped, data loss could occur.
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
An error occurred while the batch was being executed.
Updating database (Failed)
*** Could not deploy package.
Warning SQL72015: The column [dbo].[Table1].[ColumnToRemove] is being dropped, data loss could occur.
Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[Table1])
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
WITH NOWAIT;