SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUBEBOLUMITEMGRUP]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUBEBOLUMITEMGRUP](
	[SUBEBOLUMITEMGRUPID] [int] IDENTITY(1,1) NOT NULL,
	[SUBEID] [int] NOT NULL,
	[BOLUMID] [int] NOT NULL,
	[ITEMGRUPID] [int] NOT NULL,
	[AYYIHESAPID] [int] NULL,
	[AYYDHESAPID] [int] NULL,
	[YTYIHESAPID] [int] NULL,
	[YTYDHESAPID] [int] NULL,
	[INDHESAPID] [int] NULL,
 CONSTRAINT [PK_SUBEBOLUMITEMGRUP] PRIMARY KEY CLUSTERED 
(
	[SUBEBOLUMITEMGRUPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SUBEBOLUMITEMGRUP]') AND name = N'IX_SUBEBOLUMITEMGRUP')
CREATE UNIQUE NONCLUSTERED INDEX [IX_SUBEBOLUMITEMGRUP] ON [dbo].[SUBEBOLUMITEMGRUP] 
(
	[SUBEID] ASC,
	[BOLUMID] ASC,
	[ITEMGRUPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SUBEBOLUMITEMGRUP_SUBEBOLUM]') AND parent_object_id = OBJECT_ID(N'[dbo].[SUBEBOLUMITEMGRUP]'))
ALTER TABLE [dbo].[SUBEBOLUMITEMGRUP]  WITH CHECK ADD  CONSTRAINT [FK_SUBEBOLUMITEMGRUP_SUBEBOLUM] FOREIGN KEY([SUBEID], [BOLUMID])
REFERENCES [dbo].[SUBEBOLUM] ([SUBEID], [BOLUMID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SUBEBOLUMITEMGRUP_SUBEBOLUM]') AND parent_object_id = OBJECT_ID(N'[dbo].[SUBEBOLUMITEMGRUP]'))
ALTER TABLE [dbo].[SUBEBOLUMITEMGRUP] CHECK CONSTRAINT [FK_SUBEBOLUMITEMGRUP_SUBEBOLUM]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SUBEBOLUMITEMGRUP_TITEMGRUP]') AND parent_object_id = OBJECT_ID(N'[dbo].[SUBEBOLUMITEMGRUP]'))
ALTER TABLE [dbo].[SUBEBOLUMITEMGRUP]  WITH CHECK ADD  CONSTRAINT [FK_SUBEBOLUMITEMGRUP_TITEMGRUP] FOREIGN KEY([ITEMGRUPID])
REFERENCES [dbo].[TITEMGRUP] ([ITEMGRUPID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SUBEBOLUMITEMGRUP_TITEMGRUP]') AND parent_object_id = OBJECT_ID(N'[dbo].[SUBEBOLUMITEMGRUP]'))
ALTER TABLE [dbo].[SUBEBOLUMITEMGRUP] CHECK CONSTRAINT [FK_SUBEBOLUMITEMGRUP_TITEMGRUP]
GO

IF NOT EXISTS (SELECT TOP 1 * FROM SUBEBOLUMITEMGRUP WHERE AYYIHESAPID IS NOT NULL OR AYYDHESAPID IS NOT NULL OR YTYIHESAPID IS NOT NULL OR YTYDHESAPID IS NOT NULL OR INDHESAPID IS NOT NULL)
INSERT INTO SUBEBOLUMITEMGRUP (SUBEID, BOLUMID, ITEMGRUPID, AYYIHESAPID, AYYDHESAPID, YTYIHESAPID, YTYDHESAPID, INDHESAPID) 
SELECT     SUBEBOLUM.SUBEID, SUBEBOLUM.BOLUMID, TBOLUMITEMGRUP.ITEMGRUPID, 
                      TBOLUMITEMGRUP.AYYIHESAPID, TBOLUMITEMGRUP.AYYDHESAPID, TBOLUMITEMGRUP.YTYIHESAPID, TBOLUMITEMGRUP.YTYDHESAPID, TBOLUMITEMGRUP.INDHESAPID
FROM         SUBEBOLUM INNER JOIN
                      TBOLUMITEMGRUP ON SUBEBOLUM.BOLUMID = TBOLUMITEMGRUP.BOLUMID
WHERE        
  NOT EXISTS (SELECT SUBEBOLUMITEMGRUPID FROM SUBEBOLUMITEMGRUP 
            WHERE SUBEID = SUBEBOLUM.SUBEID AND BOLUMID = SUBEBOLUM.BOLUMID AND ITEMGRUPID = TBOLUMITEMGRUP.ITEMGRUPID) 
GO                      