SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STOKGRUPSUBE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STOKGRUPSUBE](
	[STOKGRUPSUBEID] [int] IDENTITY(1,1) NOT NULL,
	[STOKGRUPKODU] [nvarchar](20) NOT NULL,
	[SUBEID] [int] NOT NULL,
	[MUHNHESAPID] [int] NULL,
	[MUHSHESAPID] [int] NULL,
	[MUHGHESAPID] [int] NULL,
	[MUHAISKHESAPID] [int] NULL,
	[MUHSISKHESAPID] [int] NULL,
	[MUHAHESAPID] [int] NULL,
 CONSTRAINT [PK_STOKGRUPSUBE] PRIMARY KEY CLUSTERED 
(
	[STOKGRUPSUBEID] 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].[STOKGRUPSUBE]') AND name = N'IX_STOKGRUPSUBE_STOKGRUPKODU_SUBEID')
CREATE NONCLUSTERED INDEX [IX_STOKGRUPSUBE_STOKGRUPKODU_SUBEID] ON [dbo].[STOKGRUPSUBE] 
(
	[STOKGRUPKODU] ASC,
	[SUBEID] 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.indexes WHERE object_id = OBJECT_ID(N'[dbo].[STOKGRUPSUBE]') AND name = N'IX_STOKGRUPSUBE_SUBEID_STOKGRUPKODU')
CREATE NONCLUSTERED INDEX [IX_STOKGRUPSUBE_SUBEID_STOKGRUPKODU] ON [dbo].[STOKGRUPSUBE] 
(
	[SUBEID] ASC,
	[STOKGRUPKODU] 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_STOKGRUPSUBE_STOKGRUP]') AND parent_object_id = OBJECT_ID(N'[dbo].[STOKGRUPSUBE]'))
ALTER TABLE [dbo].[STOKGRUPSUBE]  WITH CHECK ADD  CONSTRAINT [FK_STOKGRUPSUBE_STOKGRUP] FOREIGN KEY([STOKGRUPKODU])
REFERENCES [dbo].[STOKGRUP] ([KODU])
GO

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

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

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

IF NOT EXISTS (SELECT TOP 1 * FROM STOKGRUPSUBE WHERE MUHNHESAPID IS NOT NULL OR MUHSHESAPID IS NOT NULL OR MUHGHESAPID IS NOT NULL OR MUHAISKHESAPID IS NOT NULL OR MUHSISKHESAPID IS NOT NULL OR MUHAHESAPID IS NOT NULL)
INSERT INTO STOKGRUPSUBE (SUBEID, STOKGRUPKODU, MUHNHESAPID, MUHSHESAPID, MUHGHESAPID, MUHAISKHESAPID, MUHSISKHESAPID, MUHAHESAPID) 
SELECT     SUBELER.SUBEID, STOKGRUP.KODU, MUHNHESAPID, MUHSHESAPID, MUHGHESAPID, MUHAISKHESAPID, MUHSISKHESAPID, MUHAHESAPID 
FROM         STOKGRUP CROSS JOIN SUBELER
WHERE (MUHNHESAPID IS NOT NULL OR MUHSHESAPID IS NOT NULL OR MUHGHESAPID IS NOT NULL OR MUHAISKHESAPID IS NOT NULL OR MUHSISKHESAPID IS NOT NULL OR MUHAHESAPID IS NOT NULL) AND
  NOT EXISTS (SELECT STOKGRUPSUBEID FROM STOKGRUPSUBE 
            WHERE SUBEID = SUBELER.SUBEID AND STOKGRUPKODU = STOKGRUP.KODU) 
GO