if db_id('AV2000_ALYANTE') is null CREATE DATABASE AV2000_ALYANTE
go
USE AV2000_ALYANTE
go
IF EXISTS(SELECT TABLE_NAME FROM AV2000_ALYANTE.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '_cdm_') DROP TABLE dbo._cdm_
go
CREATE TABLE dbo._cdm_ (ID INT IDENTITY(0,1) PRIMARY KEY, EVENTO VARCHAR(4000),DATA_ORA DATETIME)
go
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Inizio elaborazione', '2022-05-19T10:44:16')
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Creazione dump', '2022-05-19T10:44:16')
go
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Esecuzione dump', GetDate())
go
IF EXISTS(SELECT TABLE_NAME FROM AV2000_ALYANTE.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ACOSVA_01') DROP TABLE dbo.ACOSVA_01
CREATE TABLE dbo.ACOSVA_01(ID_AZI varchar(2),[CODMAG] smallint,[DTAFIN] int,[DTAINI] int,[CODART] varchar(015),[ANNATA] int,[COSSTD] decimal(012,5),[VALRIM] decimal(012,5),[ZERSTD] varchar(001),[ZERRIM] varchar(001),[PERRIT] decimal(006,2))
go
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Esecuzione dump: popolamento tabella ACOSVA', GetDate())
INSERT INTO dbo.ACOSVA_01 VALUES
('02',0,0,0,'  014805100S000',2016,0,0.9,'','',0 )
,('02',0,0,0,'  304005400S000',2016,0,1.5,'','',0 )
,('02',0,0,0,'  304105400S000',2016,0,1.5,'','',0 )
,('02',0,0,0,'  304203900S000',0,0,2.6,'','',0 )
,('02',0,0,0,'  304203900S000',2016,0,2.6,'','',0 )
,('02',0,0,0,'  309003900S000',2016,0,1.1,'','',0 )
,('02',0,0,0,'  EF060629Q0041',0,0,0.0315,'','',0 )
,('02',0,0,0,'  EF410549D0041',0,0,0.035,'','',0 )
,('02',0,0,0,'  EF480519D0041',0,0,0.0315,'','',0 )
,('02',0,0,0,'  ER060629Q0041',0,0,0.0315,'','',0 )
,('02',0,0,0,'  ER410549D0041',0,0,0.035,'','',0 )
,('02',0,0,0,'  ER480519D0041',0,0,0.0315,'','',0 )
,('02',0,20171231,20160101,'  014805100S000',2016,0,0.9,'','',0 )
,('02',0,20171231,20160101,'  304005400S000',2016,0,1.5,'','',0 )
,('02',0,20171231,20160101,'  304105400S000',2016,0,1.5,'','',0 )
,('02',0,20171231,20160101,'  304203900S000',2015,0,2,'','',0 )
,('02',0,20171231,20160101,'  304203900S000',2016,0,2.6,'','',0 )
,('02',0,20171231,20160101,'  CPP3P4100PN01',0,0,0.02586,'','',0 )
,('02',0,20171231,20160101,'  EF050229Q0041',0,0,0.0725,'','',0 )
,('02',0,20171231,20160101,'  EF060629Q0041',0,0,0.0725,'','',0 )
,('02',0,20171231,20160101,'  EF100469Q0041',0,0,0.041,'','',0 )
,('02',0,20171231,20160101,'  EF480519D0041',0,0,0.04,'','',0 )
,('02',0,20171231,20160101,'  ER050229Q0041',0,0,0.0725,'','',0 )
,('02',0,20171231,20160101,'  ER060629Q0041',0,0,0.0725,'','',0 )
,('02',0,20171231,20160101,'  ER100469Q0041',0,0,0.041,'','',0 )
,('02',0,20171231,20160101,'  ER480519D0041',0,0,0.04,'','',0 )
,('02',0,99991231,20160101,'  EF050229Q0041',0,0,0.0315,'','',0 )
,('02',0,99991231,20160101,'  ER050229Q0041',0,0,0.0315,'','',0 )
,('02',1,0,0,'  010904100S000',2018,0,1.2,'','',0 )
,('02',1,0,0,'  011104000S000',2018,0,1.3,'','',0 )
,('02',1,0,0,'  0200000000000',0,0,0.19,'','',0 )
,('02',1,0,0,'  ALA300000AD00',0,0,0.1,'','',0 )
,('02',1,0,0,'  CPP3P349APG01',0,0,0.0373,'','',0 )
,('02',1,0,0,'  CPP3P418E0040',0,0,0.028,'','',0 )
,('02',1,0,0,'  CPP3P5800PA01',0,0,0.029,'','',0 )
,('02',1,0,0,'  EF010419A0041',0,0,0.023,'','',0 )
,('02',1,0,0,'  EF090418E0040',0,0,0.028,'','',0 )
,('02',1,0,0,'  EF100468E0040',0,0,0.044,'','',0 )
,('02',1,0,0,'  EF420468A0041',0,0,0.028,'','',0 )
,('02',1,0,0,'  EF500638E0040',0,0,0.044,'','',0 )
,('02',1,0,0,'  EF610419A0041',0,0,0.025,'','',0 )
,('02',1,0,0,'  EF770418E0640',0,0,0.028,'','',0 )
,('02',1,0,0,'  ER010419A0041',0,0,0.02,'','',0 )
,('02',1,0,0,'  ER100468E0040',0,0,0.02,'','',0 )
,('02',1,0,0,'  ER420468A0041',0,0,0.02,'','',0 )
,('02',1,0,0,'  ER500638E0040',0,0,0.02,'','',0 )
,('02',1,0,0,'  ER610419A0041',0,0,0.02,'','',0 )
,('02',1,0,0,'  ER770418E0640',0,0,0.02,'','',0 )
,('02',2,0,0,'  010904100S000',2018,0,1.2,'','',0 )
,('02',2,0,0,'  011104000S000',2018,0,1.3,'','',0 )
,('02',2,0,0,'  0200000000000',0,0,0.19,'','',0 )
,('02',2,0,0,'  ALA300000AD00',0,0,0.1,'','',0 )
,('02',2,0,0,'  CPP3P349APG01',0,0,0.0373,'','',0 )
,('02',2,0,0,'  CPP3P418E0040',0,0,0.028,'','',0 )
,('02',2,0,0,'  CPP3P5800PA01',0,0,0.029,'','',0 )
,('02',2,0,0,'  EF010419A0041',0,0,0.023,'','',0 )
,('02',2,0,0,'  EF090418E0040',0,0,0.028,'','',0 )
,('02',2,0,0,'  EF100468E0040',0,0,0.044,'','',0 )
,('02',2,0,0,'  EF420468A0041',0,0,0.028,'','',0 )
,('02',2,0,0,'  EF500638E0040',0,0,0.044,'','',0 )
,('02',2,0,0,'  EF610419A0041',0,0,0.025,'','',0 )
,('02',2,0,0,'  EF770418E0640',0,0,0.028,'','',0 )
,('02',2,0,0,'  ER010419A0041',0,0,0.02,'','',0 )
,('02',2,0,0,'  ER100468E0040',0,0,0.02,'','',0 )
,('02',2,0,0,'  ER420468A0041',0,0,0.02,'','',0 )
,('02',2,0,0,'  ER500638E0040',0,0,0.02,'','',0 )
,('02',2,0,0,'  ER610419A0041',0,0,0.02,'','',0 )
,('02',2,0,0,'  ER770418E0640',0,0,0.02,'','',0 )
go
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Esecuzione dump: creazione br-indici tabella ACOSVA_01', GetDate())
BEGIN TRY
CREATE UNIQUE INDEX KEY01 ON dbo.ACOSVA_01 (ID_AZI,CODMAG,DTAFIN,DTAINI,CODART,ANNATA)
END TRY
BEGIN CATCH
INSERT INTO dbo._cdm_ SELECT ERROR_MESSAGE(), GETDATE()
END CATCH;
BEGIN TRY
CREATE INDEX KEY02 ON dbo.ACOSVA_01 (ID_AZI,CODMAG,CODART,ANNATA,DTAFIN,DTAINI)
END TRY
BEGIN CATCH
INSERT INTO dbo._cdm_ SELECT ERROR_MESSAGE(), GETDATE()
END CATCH;
go
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Fine elaborazione', GetDate())
go
