/****** 2022-11-07 Version 2022.11.0 ******/

/****** Stop all Windows Services related to JTB ******/
/****** EXISTINGDATABASE is an existing database ******/
/****** NEWDATABASE will be created and data will be copied from EXISTINGDATABASE ******/
/****** Search and replace NEWDATABASE with the name of new database ******/
/****** Search and replace EXISTINGDATABASE with the name of the existing database ******/
/****** After update you can rename the old existing database to a backup name and the new database to same as the old database. If not you need to configure JTB FlexReport Core and Service to use the new database. ******/
/****** After it is done, run the Service Configurator, run Clear and then Sampling All Records. ******/
/****** Start all Windows Services related to JTB ******/

USE [master]  
GO 
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'NEWDATABASE') 
DROP DATABASE [NEWDATABASE] 
GO
CREATE DATABASE [NEWDATABASE]  
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
USE [NEWDATABASE]  
GO 
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
PRINT N'Creating UserDetailedLogTemp.'; 
CREATE TABLE [dbo].[UserDetailedLogTemp](
	[ServerName] [int] NULL,
	[UserName] [int] NULL,
	[UserHost] [int] NULL,
	[AppName] [int] NULL,
	[DateField] [datetime] NULL,
	[LingerValue] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp]
(
	[DateField] ASC
)
INCLUDE ( 	[ServerName],
	[UserName],
	[UserHost],
	[AppName],
	[LingerValue],
	[Licenses],
	[ProjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp]
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp]
(
	[ServerName] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[Licenses] ASC,
	[LingerValue] ASC,
	[UserHost] ASC,
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp_UserName] ON [dbo].[UserDetailedLogTemp] ([UserName]) INCLUDE ([ServerName],[UserHost],[AppName])
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTempOldNewDate](
	[Oldestdate] [datetime] NULL,
	[Newestdate] [datetime] NULL
) ON [PRIMARY]
GO

PRINT N'Creating SamplingInterval.'; 
CREATE TABLE [dbo].[SamplingInterval](
	[SamplingInterval] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempAppName(AppName nvarchar(100),Description nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempServerName(ServerName nvarchar(100),Description nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempFeatureGroups(AppGroupName nvarchar(100),AppName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempServerGroups(ServerGroupName nvarchar(100),ServerName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempFeatureServer(AppName nvarchar(100),ServerName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempDetailedLog(AppName nvarchar(100),ServerName nvarchar(100),UserName nvarchar(100),HostName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempUserName(UserName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempHostName(HostDesc nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempGroupName(UserName nvarchar(100),GroupId int,UserDescription nvarchar(100),GroupName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempHostGroupName(HostName nvarchar(100),HostGroupId int,HostDescription nvarchar(100),HostGroupName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempOldNewDate(Oldestdate datetime,Newestdate datetime)
go
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserNameHostGroup.'; 
CREATE TABLE [dbo].[tblUserNameHostGroup](
	[HostName] [int] NOT NULL,
	[HostGroupId] [int] NOT NULL,
	[FromDate] [datetime] NULL,
	[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameHostGroup]
(
	[HostName] ASC,
	[HostGroupId] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserNameGroup.'; 
CREATE TABLE [dbo].[tblUserNameGroup](
	[UserName] [int] NOT NULL,
	[GroupId] [int] NOT NULL,
	[FromDate] [datetime] NULL,
	[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameGroup]
(
	[UserName] ASC,
	[GroupId] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserDetails.';
CREATE TABLE [dbo].[tblUserDetails](
[UserName] [nvarchar](100) NOT NULL,
	[UserDescription1] [nvarchar](255) NULL,
	[UserDescription2] [nvarchar](255) NULL,
	[UserDescription3] [nvarchar](255) NULL,
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	 CONSTRAINT [PK_tblUserDetails_1] PRIMARY KEY CLUSTERED 
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_tblUserDetails] UNIQUE NONCLUSTERED 
(
	[UserName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserDetailedLogPrev.';
CREATE TABLE [dbo].[tblUserDetailedLogPrev](
	[ServerName] [nvarchar](100) NULL,
	[UserName] [nvarchar](100) NULL,
	[UserHost] [nvarchar](100) NULL,
	[AppName] [nvarchar](100) NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL,
	[CheckedOutDateOrg] [datetime] NULL,
	[LicenseSystem] [nvarchar](100) NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLogPrev]
(
	[CheckedOutDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblUserDetailedLogprev_Datefield] ON [dbo].[tblUserDetailedLogprev] ([DateField] ASC) INCLUDE( [CheckedOutDate]) WITH(SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON[PRIMARY]
GO
CREATE NONCLUSTERED INDEX _IX_tblUserDetailedLog_AppName_DateField ON [dbo].[tblUserDetailedLog]([AppName], [DateField]) INCLUDE([LicHandle])
GO

PRINT N'Creating tblUserDetailedLog.';
CREATE TABLE [dbo].[tblUserDetailedLog](
	[ServerName] [nvarchar](100) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[UserHost] [nvarchar](100) NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserDetailedLog]
(
	[ServerName] ASC,
	[UserName] ASC,
	[UserHost] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[CheckedOutDate] ASC,
	[LicHandle] 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
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLog]
(
	[CheckedOutDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblUserDetailedLog_DateField] ON [dbo].[tblUserDetailedLog] ([DateField] ASC) INCLUDE( [CheckedOutDate]) WITH(SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON[PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserCompleteDetailedLog.';
CREATE TABLE [dbo].[tblUserCompleteDetailedLog](
	[ServerName] [nvarchar](100) NULL,
	[UserName] [nvarchar](100) NULL,
	[UserHost] [nvarchar](100) NULL,
	[AppName] [nvarchar](100) NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [defindex] ON [dbo].[tblUserCompleteDetailedLog]
(
	[DateField] ASC,
	[ServerName] ASC,
	[UserName] ASC,
	[UserHost] ASC,
	[AppName] ASC,
	[CheckedOutDate] ASC,
	[LicHandle] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblServerNames.';
CREATE TABLE [dbo].[tblServerNames](
	[ServerName] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
	[EmailNotificationPing] [bit] NULL,
	[ServerID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblServerNames_1] PRIMARY KEY CLUSTERED 
(
	[ServerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblServerNames] UNIQUE NONCLUSTERED 
(
	[ServerName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblServerGroupsJunction.';
CREATE TABLE [dbo].[tblServerGroupsJunction](
	[ServerGroupName] [nvarchar](100) NOT NULL,
	[ServerName] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblServerGroupsJunction]
(
	[ServerGroupName] ASC,
	[ServerName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblServerGroups.';
CREATE TABLE [dbo].[tblServerGroups](
	[ServerGroupName] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
	[ServerGroupID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblServerGroups_1] PRIMARY KEY CLUSTERED 
(
	[ServerGroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblServerGroups] UNIQUE NONCLUSTERED 
(
	[ServerGroupName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblPingedHosts.';
CREATE TABLE [dbo].[tblPingedHosts](
	[ServerName] [nvarchar](100) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[UserHost] [nvarchar](100) NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[CheckedOutDate] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[PingDate] [datetime] NULL,
	[PingResult] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblHostGroups.';
CREATE TABLE [dbo].[tblHostGroups](
	[HostGroupId] [int] IDENTITY(1,1) NOT NULL,
	[HostGroupName] [nvarchar](100) NOT NULL,
	[HostGroupDescription] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
 CONSTRAINT [PK_tblHostGroups] PRIMARY KEY CLUSTERED 
(
	[HostGroupId] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblHostDetails.';
CREATE TABLE [dbo].[tblHostDetails](
	[HostName] [nvarchar](100) NOT NULL,
	[HostDescription1] [nvarchar](255) NULL,
	[HostDescription2] [nvarchar](255) NULL,
	[HostIPv4] [nvarchar](15) NULL,
	[HostIPv6] [nvarchar](39) NULL,
	[HostID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblHostDetails_1] PRIMARY KEY CLUSTERED 
(
	[HostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblHostDetails] UNIQUE NONCLUSTERED 
(
	[HostName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblGroups.';
CREATE TABLE [dbo].[tblGroups](
	[GroupId] [int] IDENTITY(1,1) NOT NULL,
	[GroupName] [nvarchar](100) NOT NULL,
	[GroupDescription] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
 CONSTRAINT [PK_tblGroups] PRIMARY KEY CLUSTERED 
(
	[GroupId] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblDeniedUserLog.';
CREATE TABLE [dbo].[tblDeniedUserLog](
	[UserName] [nvarchar](100) NOT NULL,
	[HostName] [nvarchar](100) NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[FirstDenial] [datetime] NULL,
	[LastDenial] [datetime] NULL,
	[OUT] [datetime] NULL,
	[Denials] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblDeniedUserLog]
(
	[UserName] ASC,
	[HostName] ASC,
	[AppName] ASC,
	[FirstDenial] ASC,
	[LastDenial] ASC,
	[OUT] ASC,
	[Denials] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblAppName.';
CREATE TABLE [dbo].[tblAppName](
	[AppName] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[NoLog] [bit] NOT NULL,
	[EmailNotificationPercentageLevel] [int] NULL,
	[EmailNotificationLicensesLeft] [int] NULL,
	[EmailNotificationLicensesMax] [int] NULL,
	[EmailNotificationDenials] [bit] NOT NULL,
	[MaxLic] [int] NULL,
	[PriceFactorPerHour] [real] NULL,
	[DisplayAsUser] [bit] NOT NULL,
	[AppId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblAppName] PRIMARY KEY CLUSTERED 
(
	[AppId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblAppName] UNIQUE NONCLUSTERED 
(
	[AppName] 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].[tblAppName] ADD  CONSTRAINT [DF_tblAppName_NoLog]  DEFAULT ((0)) FOR [NoLog]
GO
ALTER TABLE [dbo].[tblAppName] ADD  CONSTRAINT [DF_tblAppName_EmailNotificationDenials]  DEFAULT ((0)) FOR [EmailNotificationDenials]
GO
ALTER TABLE [dbo].[tblAppName] ADD  CONSTRAINT [DF_tblAppName_DisplayAsUser]  DEFAULT ((0)) FOR [DisplayAsUser]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblAppGroupsJunction.';
CREATE TABLE [dbo].[tblAppGroupsJunction](
	[AppGroupName] [nvarchar](100) NOT NULL,
	[AppName] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblAppGroupsJunction]
(
	[AppGroupName] ASC,
	[AppName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblAppGroups.';
CREATE TABLE [dbo].[tblAppGroups](
	[AppGroupName] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
	[AppGroupID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblAppGroups_1] PRIMARY KEY CLUSTERED 
(
	[AppGroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblAppGroups] UNIQUE NONCLUSTERED 
(
	[AppGroupName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating log.';
CREATE TABLE [dbo].[log](
	[ServerName] [nvarchar](100) NOT NULL,
	[DateTime] [datetime] NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[MaxLic] [int] NULL,
	[UsedLic] [int] NULL,
	[Linger] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[log]
(
	[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[log]
(
	[ServerName] ASC,
	[DateTime] ASC,
	[AppName] 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
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblWildcard.';
CREATE TABLE [dbo].[tblWildcard](
	[ItemType] [nvarchar](10) NOT NULL,
	[Wildcard] [nvarchar](100) NOT NULL,
	[ItemGroup] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblProjects.';
CREATE TABLE [dbo].[tblProjects](
	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
	[Project] [nvarchar](50) NOT NULL,
	[IsActive] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProjects] ADD  CONSTRAINT [DF_tblProjects_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblProjects]
(
	[Project] 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

USE [EXISTINGDATABASE]  
GO
DROP TABLE [dbo].[UserDetailedLogTemp]
GO

PRINT N'Creating UserDetailedLogTemp.';
CREATE TABLE [dbo].[UserDetailedLogTemp](
	[ServerName] [int] NULL,
	[UserName] [int] NULL,
	[UserHost] [int] NULL,
	[AppName] [int] NULL,
	[DateField] [datetime] NULL,
	[LingerValue] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp]
(
	[DateField] ASC
)
INCLUDE ( 	[ServerName],
	[UserName],
	[UserHost],
	[AppName],
	[LingerValue],
	[Licenses],
	[ProjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp]
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp]
(
	[ServerName] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[Licenses] ASC,
	[LingerValue] ASC,
	[UserHost] ASC,
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

BEGIN TRY  
    CREATE TABLE tblWildcard (ItemType nvarchar(10) NOT NULL,Wildcard nvarchar(100) NOT NULL,ItemGroup nvarchar(100) NOT NULL)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE TABLE SamplingInterval (SamplingInterval int NULL)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostDetails ADD HostIPv4 nvarchar(15)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostDetails ADD HostIPv6 nvarchar(39)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE TABLE tblProjects (ProjectID int IDENTITY(1,1) NOT NULL CONSTRAINT PKeyMyId PRIMARY KEY,Project nvarchar(50) NOT NULL,IsActive smallint NOT NULL)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblProjects ADD  CONSTRAINT DF_tblProjects_IsActive  DEFAULT ((1)) FOR IsActive
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE UNIQUE INDEX unique1 ON tblProjects (Project)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE TABLE tblUserCompleteDetailedLog (ServerName nvarchar(100), UserName nvarchar(100), UserHost nvarchar(100), AppName nvarchar(100), DateField datetime, LingerDate datetime, CheckedOutDate datetime, LicHandle INTEGER, Licenses INTEGER)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX defindex ON tblUserCompleteDetailedLog (DateField, ServerName, UserName, UserHost, AppName, CheckedOutDate,LicHandle)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX dt ON dbo.log(DateTime)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblUserDetailedLog ADD ProjectID int
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX dt ON dbo.tblUserDetailedLog(CheckedOutDate)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblUserDetailedLogPrev ADD ProjectID int
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX dt ON dbo.tblUserDetailedLogPrev(CheckedOutDate)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD DisplayAsUser bit DEFAULT 0
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_NoLog  DEFAULT ((0)) FOR NoLog
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_EmailNotificationDenials  DEFAULT ((0)) FOR EmailNotificationDenials
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_DisplayAsUser  DEFAULT ((0)) FOR DisplayAsUser
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostGroups ADD MaxLic int
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblGroups Drop CONSTRAINT PK_tblGroups
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblGroups Drop Column GroupId
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblGroups ADD GroupId int IDENTITY(1,1) NOT NULL
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblGroups WITH NOCHECK ADD CONSTRAINT PK_tblGroups PRIMARY KEY CLUSTERED (GroupId ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblHostGroups Drop CONSTRAINT PK_tblHostGroups
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblHostGroups Drop Column HostGroupId
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostGroups ADD HostGroupId int IDENTITY(1,1) NOT NULL
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostGroups WITH NOCHECK ADD CONSTRAINT PK_tblHostGroups PRIMARY KEY CLUSTERED (HostGroupId ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp] ([ID])
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp] ([ServerName] ASC,[AppName] ASC,[DateField] ASC,[Licenses] ASC,[LingerValue] ASC,[UserHost] ASC,[UserName] ASC)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp] ([DateField]) INCLUDE ([ServerName],[UserName],[UserHost],[AppName],[LingerValue],[Licenses],[ProjectID])
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

PRINT N'Copy data from old database.';
USE [NEWDATABASE]  
GO 

PRINT N'Copy data to table log.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.log') AND type in (N'U'))
INSERT dbo.log
SELECT [ServerName]
      ,[DateTime]
      ,[AppName]
      ,MAX(MaxLic) AS Expr1
      ,MAX(UsedLic) AS Expr2
      ,MAX(Linger) AS Expr3
  FROM [EXISTINGDATABASE].[dbo].[log]
  GROUP BY ServerName, DateTime, AppName
GO

PRINT N'Copy data to table tblAppName.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblAppName') AND type in (N'U'))
INSERT dbo.tblAppName
SELECT [AppName]
      ,MAX([Description]) AS Expr1
      ,CAST(MAX(CAST([NoLog] as INT)) AS BIT) AS Expr2
      ,MAX([EmailNotificationPercentageLevel]) AS Expr3
      ,MAX([EmailNotificationLicensesLeft]) AS Expr4
      ,MAX([EmailNotificationLicensesMax]) AS Expr5
      ,CAST(MAX(CAST(ISNULL([EmailNotificationDenials],0) as INT)) AS BIT) AS Expr6
      ,MAX([MaxLic]) AS Expr7
      ,MAX([PriceFactorPerHour]) AS Expr8
      ,CAST(MAX(CAST(ISNULL([DisplayAsUser],0) as INT)) AS BIT) AS Expr9
  FROM [EXISTINGDATABASE].[dbo].[tblAppName]
  GROUP BY [AppName]
GO

PRINT N'Copy data to table tblAppGroups.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblAppGroups') AND type in (N'U'))
INSERT dbo.tblAppGroups
SELECT [AppGroupName]
      ,MAX([Description]) AS Expr1
      ,MAX([MaxLic]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblAppGroups]
  GROUP BY [AppGroupName]
GO

PRINT N'Copy data to table tblAppGroupsJunction.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblAppGroupsJunction') AND type in (N'U'))
INSERT dbo.tblAppGroupsJunction
SELECT [AppGroupName]
      ,an.[AppId]
  FROM [EXISTINGDATABASE].[dbo].[tblAppGroupsJunction] as aj
inner join [NEWDATABASE].dbo.tblAppName (nolock) an 
on an.AppName COLLATE SQL_latin1_general_CP1_ci_as = aj.AppName
  GROUP BY aj.[AppGroupName],an.[AppId]
GO

PRINT N'Copy data to table tblDeniedUserLog.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblDeniedUserLog') AND type in (N'U'))
INSERT dbo.tblDeniedUserLog
SELECT DISTINCT [UserName]
      ,[HostName]
      ,[AppName]
      ,[FirstDenial]
      ,[LastDenial]
      ,[OUT]
      ,[Denials]
  FROM [EXISTINGDATABASE].[dbo].[tblDeniedUserLog]
GO

SET IDENTITY_INSERT dbo.tblGroups ON
GO

PRINT N'Copy data to table tblGroups.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblGroups') AND type in (N'U'))
INSERT dbo.tblGroups
([GroupId]
,[GroupName]
,[GroupDescription]
,[MaxLic]
)
SELECT [GroupId]
      ,[GroupName]
      ,[GroupDescription]
      ,[MaxLic]
  FROM [EXISTINGDATABASE].[dbo].[tblGroups]
GO
SET IDENTITY_INSERT dbo.tblGroups OFF
GO

PRINT N'Copy data to table tblHostDetails.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblHostDetails') AND type in (N'U'))
INSERT dbo.tblHostDetails
SELECT [HostName]
      ,MAX([HostDescription1]) AS Expr1
      ,MAX([HostDescription2]) AS Expr2
      ,MAX([HostIPv4]) AS Expr3
      ,MAX([HostIPv6]) AS Expr4
  FROM [EXISTINGDATABASE].[dbo].[tblHostDetails]
  GROUP BY [HostName]
GO

SET IDENTITY_INSERT dbo.tblHostGroups ON
GO

PRINT N'Copy data to table tblHostGroups.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblHostGroups') AND type in (N'U'))
INSERT dbo.tblHostGroups
([HostGroupId]
,[HostGroupName]
,[HostGroupDescription]
,[MaxLic]
)
SELECT [HostGroupId]
      ,[HostGroupName]
      ,[HostGroupDescription]
      ,[MaxLic]
  FROM [EXISTINGDATABASE].[dbo].[tblHostGroups]
GO
SET IDENTITY_INSERT dbo.tblHostGroups OFF
GO

PRINT N'Copy data to table tblPingedHosts.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblPingedHosts') AND type in (N'U'))
INSERT dbo.tblPingedHosts
SELECT DISTINCT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[CheckedOutDate]
      ,[LingerDate]
      ,[LicHandle]
      ,[PingDate]
      ,[PingResult]
  FROM [EXISTINGDATABASE].[dbo].[tblPingedHosts]
GO

PRINT N'Copy data to table tblProjects.';
SET IDENTITY_INSERT dbo.tblProjects ON
GO
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblProjects') AND type in (N'U'))
INSERT dbo.tblProjects
([ProjectID]
,[Project]
,[IsActive]
)
SELECT MAX([ProjectID]) AS Expr1
      ,[Project]
      ,MAX([IsActive]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblProjects]
  GROUP BY [Project]
GO
SET IDENTITY_INSERT dbo.tblProjects OFF
GO

PRINT N'Copy data to table tblServerNames.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblServerNames') AND type in (N'U'))
INSERT dbo.tblServerNames
SELECT [ServerName]
      ,MAX([Description]) AS Expr1
      ,MAX([MaxLic]) AS Expr2
      ,CAST(MAX(CAST([EmailNotificationPing] as INT)) AS BIT) AS Expr3
  FROM [EXISTINGDATABASE].[dbo].[tblServerNames]
  GROUP BY [ServerName]
GO

PRINT N'Copy data to table tblServerGroups.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblServerGroups') AND type in (N'U'))
INSERT dbo.tblServerGroups
SELECT [ServerGroupName]
      ,MAX([Description]) AS Expr1
      ,MAX([MaxLic]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblServerGroups]
  GROUP BY [ServerGroupName]
GO

PRINT N'Copy data to table tblServerGroupsJunction.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblServerGroupsJunction') AND type in (N'U'))
INSERT dbo.tblServerGroupsJunction
SELECT [ServerGroupName]
      ,sn.[ServerId]
  FROM [EXISTINGDATABASE].[dbo].[tblServerGroupsJunction] as sj
inner join [NEWDATABASE].dbo.tblServerNames (nolock) sn 
on sn.ServerName COLLATE SQL_latin1_general_CP1_ci_as = sj.ServerName
  GROUP BY sj.[ServerGroupName],sn.[ServerId]
GO

PRINT N'Copy data to table tblUserCompleteDetailedLog.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserCompleteDetailedLog') AND type in (N'U'))
INSERT dbo.tblUserCompleteDetailedLog
SELECT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,MAX([LingerDate]) AS Expr1
      ,[CheckedOutDate]
      ,[LicHandle]
      ,MAX([Licenses]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblUserCompleteDetailedLog]
  GROUP BY [DateField],[ServerName],[UserName],[UserHost],[AppName],[CheckedOutDate],[LicHandle]
GO

SET IDENTITY_INSERT dbo.tblUserDetailedLog ON
GO

PRINT N'Copy data to table tblUserDetailedLog.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserDetailedLog') AND type in (N'U'))
INSERT dbo.tblUserDetailedLog
([ServerName]
,[UserName]
,[UserHost]
,[AppName]
,[DateField]
,[LingerDate]
,[CheckedOutDate]
,[LicHandle]
,[Licenses]
,[ID]
,[ProjectID]
)
SELECT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,MAX([LingerDate]) AS Expr1
      ,[CheckedOutDate]
      ,[LicHandle]
      ,MAX([Licenses]) AS Expr2
      ,MAX([ID]) AS Expr3
      ,MAX([ProjectID]) AS Expr4
  FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLog]
  GROUP BY [ServerName],[UserName],[UserHost],[AppName],[DateField],[CheckedOutDate],[LicHandle]
GO
SET IDENTITY_INSERT dbo.tblUserDetailedLog OFF
GO

PRINT N'Copy data to table tblUserDetailedLogPrev.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserDetailedLogPrev') AND type in (N'U'))
INSERT dbo.tblUserDetailedLogPrev
SELECT DISTINCT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,[LingerDate]
      ,[CheckedOutDate]
      ,[LicHandle]
      ,[Licenses]
      ,[CheckedOutDateOrg]
      ,[LicenseSystem]
      ,[ProjectID]
  FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLogPrev]
GO

PRINT N'Copy data to table tblUserDetails.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserDetails') AND type in (N'U'))
INSERT dbo.tblUserDetails
SELECT [UserName]
      ,MAX([UserDescription1]) AS Expr1
      ,MAX([UserDescription2]) AS Expr2
      ,MAX([UserDescription3]) AS Expr3
  FROM [EXISTINGDATABASE].[dbo].[tblUserDetails]
  GROUP BY [UserName]
GO

PRINT N'Copy data to table tblUserNameGroup.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserNameGroup') AND type in (N'U'))
INSERT dbo.tblUserNameGroup
SELECT ud.[UserID] as [UserName]
      ,ung.[GroupId]
      ,MAX(ung.[FromDate]) AS Expr1
      ,MAX(ung.[ToDate]) AS Expr2 from [EXISTINGDATABASE].dbo.tblUserNameGroup (nolock) ung 
inner join [NEWDATABASE].dbo.tblUserDetails (nolock) ud on ud.UserName COLLATE SQL_latin1_general_CP1_ci_as = ung.UserName
GROUP BY ud.[UserID],ung.[GroupId]
GO

PRINT N'Copy data to table tblUserNameHostGroup.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserNameHostGroup') AND type in (N'U'))
INSERT dbo.tblUserNameHostGroup
SELECT hd.[HostID] as [HostName]
      ,uhg.[HostGroupId]
      ,MAX(uhg.[FromDate]) AS Expr1
      ,MAX(uhg.[ToDate]) AS Expr2 from [EXISTINGDATABASE].dbo.tblUserNameHostGroup (nolock) uhg 
inner join [NEWDATABASE].dbo.tblHostDetails (nolock) hd on hd.HostName COLLATE SQL_latin1_general_CP1_ci_as = uhg.HostName
GROUP BY hd.[HostID],uhg.[HostGroupId]
GO

PRINT N'Copy data to table tblWildcard.';
IF EXISTS (SELECT * FROM [EXISTINGDATABASE].sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblWildcard') AND type in (N'U'))
INSERT dbo.tblWildcard
SELECT DISTINCT [ItemType]
      ,[Wildcard]
      ,[ItemGroup]
  FROM [EXISTINGDATABASE].[dbo].[tblWildcard]
GO