123 lines
3.3 KiB
Transact-SQL
123 lines
3.3 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
IF (EXISTS (SELECT *
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_NAME = 'Department'))
|
|
set noexec on
|
|
|
|
begin transaction
|
|
|
|
CREATE TABLE [dbo].[Department](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[Name] [nvarchar](200) NOT NULL,
|
|
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [DF_Department_Id] DEFAULT (newid()) FOR [Id]
|
|
GO
|
|
|
|
|
|
Insert into [dbo].[Department]
|
|
select id, name from dbo.GLAccount;
|
|
GO
|
|
|
|
|
|
alter table [dbo].[Team] drop [FK_Team_GLDepartment]
|
|
go
|
|
|
|
alter table [dbo].[Team] add DepartmentId uniqueidentifier null
|
|
go
|
|
|
|
update [dbo].[Team] set DepartmentId = GLAccountId
|
|
|
|
|
|
ALTER TABLE [dbo].[Team] WITH CHECK ADD CONSTRAINT [FK_Team_Department] FOREIGN KEY([DepartmentId])
|
|
REFERENCES [dbo].[Department] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Team] CHECK CONSTRAINT [FK_Team_Department]
|
|
GO
|
|
|
|
|
|
|
|
alter table [dbo].[Company2GLDepartment] add DepartmentId uniqueidentifier null
|
|
go
|
|
|
|
update [dbo].[Company2GLDepartment] set DepartmentId = GLAccountId --(select top(1) id from [dbo].[Department])
|
|
|
|
|
|
ALTER TABLE [dbo].[Company2GLDepartment] WITH CHECK ADD CONSTRAINT [FK_Company2Department_Department] FOREIGN KEY([DepartmentId])
|
|
REFERENCES [dbo].[Department] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Company2GLDepartment] CHECK CONSTRAINT [FK_Company2Department_Department]
|
|
GO
|
|
|
|
|
|
|
|
|
|
alter table [dbo].[User2GLDepartment] add DepartmentId uniqueidentifier null
|
|
go
|
|
|
|
update [dbo].[User2GLDepartment] set DepartmentId = GLDepartmentId
|
|
|
|
|
|
ALTER TABLE [dbo].[User2GLDepartment] WITH CHECK ADD CONSTRAINT [FK_User2Department_Department] FOREIGN KEY([DepartmentId])
|
|
REFERENCES [dbo].[Department] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[User2GLDepartment] CHECK CONSTRAINT [FK_User2Department_Department]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[Department2CostCenter](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[DepartmentId] [uniqueidentifier] NOT NULL,
|
|
[CostCenterId] [uniqueidentifier] NOT NULL,
|
|
CONSTRAINT [PK_Department2CostCenter] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Department2CostCenter] ADD CONSTRAINT [DF_Department2CostCenter_Id] DEFAULT (newid()) FOR [Id]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Department2CostCenter] WITH CHECK ADD CONSTRAINT [FK_Department2CostCenter_CreditDepartment] FOREIGN KEY([CostCenterId])
|
|
REFERENCES [dbo].[CreditDepartment] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Department2CostCenter] CHECK CONSTRAINT [FK_Department2CostCenter_CreditDepartment]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Department2CostCenter] WITH CHECK ADD CONSTRAINT [FK_Department2CostCenter_Department] FOREIGN KEY([DepartmentId])
|
|
REFERENCES [dbo].[Department] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Department2CostCenter] CHECK CONSTRAINT [FK_Department2CostCenter_Department]
|
|
GO
|
|
|
|
|
|
|
|
|
|
ALTER TABLE [dbo].[Team] DROP COLUMN GlAccountId
|
|
go
|
|
|
|
ALTER TABLE [dbo].[Company2GLDepartment] DROP CONSTRAINT FK_Company2GLDepartment_GLDepartmentId
|
|
go
|
|
alter table [dbo].[Company2GLDepartment] DROP COLUMN GlAccountId
|
|
go
|
|
|
|
ALTER TABLE [dbo].[User2GLDepartment] DROP CONSTRAINT FK_User2GLDepartment_GLDepartment
|
|
go
|
|
ALTER TABLE [dbo].[User2GLDepartment] DROP COLUMN GlDepartmentId
|
|
go
|
|
|
|
|
|
commit transaction |