SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETS_CARISUBE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ETS_CARISUBE](
	[ETSCARISUBEID] [int] IDENTITY(1,1) NOT NULL,
	[ETSCARIID] [int] NOT NULL,
	[SUBEID] [int] NOT NULL,
	[HESAPID] [int] NULL,
	[ALISHESAPID] [int] NULL,
 CONSTRAINT [PK_ETS_CARISUBE] PRIMARY KEY CLUSTERED 
(
	[ETSCARISUBEID] 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].[ETS_CARISUBE]') AND name = N'IX_ETS_CARISUBE_ETSCARIID_SUBEID')
CREATE NONCLUSTERED INDEX [IX_ETS_CARISUBE_ETSCARIID_SUBEID] ON [dbo].[ETS_CARISUBE] 
(
	[ETSCARIID] 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].[ETS_CARISUBE]') AND name = N'IX_ETS_CARISUBE_SUBEID_ETSCARIID')
CREATE NONCLUSTERED INDEX [IX_ETS_CARISUBE_SUBEID_ETSCARIID] ON [dbo].[ETS_CARISUBE] 
(
	[SUBEID] ASC,
	[ETSCARIID] 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_ETS_CARISUBE_ETS_CARI]') AND parent_object_id = OBJECT_ID(N'[dbo].[ETS_CARISUBE]'))
ALTER TABLE [dbo].[ETS_CARISUBE]  WITH CHECK ADD  CONSTRAINT [FK_ETS_CARISUBE_ETS_CARI] FOREIGN KEY([ETSCARIID])
REFERENCES [dbo].[ETS_CARI] ([CARIID])
GO

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

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

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

IF NOT EXISTS (SELECT TOP 1 * FROM ETS_CARISUBE WHERE HESAPID IS NOT NULL OR ALISHESAPID IS NOT NULL)
INSERT INTO ETS_CARISUBE (SUBEID, ETSCARIID, HESAPID, ALISHESAPID) 
SELECT     SUBELER.SUBEID, CARIID, HESAPID, ALISHESAPID
FROM         ETS_CARI CROSS JOIN SUBELER
WHERE (HESAPID IS NOT NULL OR ALISHESAPID IS NOT NULL) AND        
  NOT EXISTS (SELECT ETSCARISUBEID FROM ETS_CARISUBE 
            WHERE SUBEID = SUBELER.SUBEID AND ETSCARIID = ETS_CARI.CARIID) 
GO                      