107 lines
3.6 KiB
Transact-SQL
107 lines
3.6 KiB
Transact-SQL
-- ============================================================
|
|
-- 多簡訊平台架構 Migration Script
|
|
-- 建立日期：2026/6/3
|
|
-- ============================================================
|
|
|
|
-- 1. 新增 MMSPlatform 資料表
|
|
-- ============================================================
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MMSPlatform')
|
|
BEGIN
|
|
CREATE TABLE [dbo].[MMSPlatform] (
|
|
[ID] INT NOT NULL IDENTITY(1,1),
|
|
[PlatformCode] NVARCHAR(20) NOT NULL,
|
|
[PlatformName] NVARCHAR(50) NOT NULL,
|
|
[Account] NVARCHAR(100) NULL,
|
|
[Password] NVARCHAR(100) NULL,
|
|
[Remark] NVARCHAR(200) NULL,
|
|
[DB_APPNO] INT NOT NULL DEFAULT 1,
|
|
[DB_CRDAT] DATETIME NOT NULL DEFAULT GETDATE(),
|
|
[DB_CRUSR] NVARCHAR(20) NOT NULL DEFAULT 'SYSTEM',
|
|
[DB_TRDAT] DATETIME NOT NULL DEFAULT GETDATE(),
|
|
[DB_TRUSR] NVARCHAR(20) NOT NULL DEFAULT 'SYSTEM',
|
|
CONSTRAINT [PK_MMSPlatform] PRIMARY KEY ([ID])
|
|
);
|
|
|
|
-- 預設插入兩個平台
|
|
INSERT INTO [dbo].[MMSPlatform] ([PlatformCode], [PlatformName], [DB_APPNO], [DB_CRDAT], [DB_CRUSR], [DB_TRDAT], [DB_TRUSR])
|
|
VALUES
|
|
('EVERY8D', 'EVERY8D', 1, GETDATE(), 'SYSTEM', GETDATE(), 'SYSTEM'),
|
|
('MITAKE', '三竹', 1, GETDATE(), 'SYSTEM', GETDATE(), 'SYSTEM');
|
|
|
|
PRINT 'MMSPlatform 資料表建立完成';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'MMSPlatform 資料表已存在，略過';
|
|
END
|
|
GO
|
|
|
|
-- 2. 新增備份表 MMSPlatform_BAK
|
|
-- ============================================================
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MMSPlatform_BAK')
|
|
BEGIN
|
|
CREATE TABLE [dbo].[MMSPlatform_BAK] (
|
|
[ID] INT NOT NULL,
|
|
[PlatformCode] NVARCHAR(20) NOT NULL,
|
|
[PlatformName] NVARCHAR(50) NOT NULL,
|
|
[Account] NVARCHAR(100) NULL,
|
|
[Password] NVARCHAR(100) NULL,
|
|
[Remark] NVARCHAR(200) NULL,
|
|
[DB_APPNO] INT NOT NULL,
|
|
[DB_CRDAT] DATETIME NOT NULL,
|
|
[DB_CRUSR] NVARCHAR(20) NOT NULL,
|
|
[DB_TRDAT] DATETIME NOT NULL,
|
|
[DB_TRUSR] NVARCHAR(20) NOT NULL,
|
|
[DB_TRMOD] NVARCHAR(10) NOT NULL
|
|
);
|
|
PRINT 'MMSPlatform_BAK 資料表建立完成';
|
|
END
|
|
GO
|
|
|
|
-- 3. ProjectMMSAccount 新增 PlatformCode 欄位
|
|
-- ============================================================
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM sys.columns
|
|
WHERE object_id = OBJECT_ID('ProjectMMSAccount') AND name = 'PlatformCode'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [dbo].[ProjectMMSAccount]
|
|
ADD [PlatformCode] NVARCHAR(20) NULL;
|
|
|
|
PRINT 'ProjectMMSAccount.PlatformCode 欄位新增完成';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'ProjectMMSAccount.PlatformCode 欄位已存在，略過';
|
|
END
|
|
GO
|
|
|
|
-- 同步備份表
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM sys.columns
|
|
WHERE object_id = OBJECT_ID('ProjectMMSAccount_BAK') AND name = 'PlatformCode'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [dbo].[ProjectMMSAccount_BAK]
|
|
ADD [PlatformCode] NVARCHAR(20) NULL;
|
|
|
|
PRINT 'ProjectMMSAccount_BAK.PlatformCode 欄位新增完成';
|
|
END
|
|
GO
|
|
|
|
-- 4. BWEX 新增 CurrentMMSPlatform 設定
|
|
-- ============================================================
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM [dbo].[BWEX] WHERE [BSNUM] = 'CurrentMMSPlatform' AND [BSITM] = ' 1'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [dbo].[BWEX] ([BSNUM], [BSITM], [BSNAM])
|
|
VALUES ('CurrentMMSPlatform', ' 1', 'EVERY8D');
|
|
|
|
PRINT 'BWEX CurrentMMSPlatform 設定新增完成';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'BWEX CurrentMMSPlatform 設定已存在，略過';
|
|
END
|
|
GO |