// - le but est de modifier les champs identity et numeric en integer

// - la table d'origine est renommé

sp_rename 'telephone','telephone3';

// - création de la table intermédiare telephonne4. Uniquement avec les champs à modifier

create table telephone4
(
ref integer not null,
old_ref numeric(18,0) null
)
;

// - insertion des donnees dans la table telephone4 à partir de telephone3. On crée un champ old_ref
// avec les valeurs de ref. Et un champs ref avec 0 car il doit être non null

insert into telephone4
(
ref,
old_ref
)

select
0,
ref

From telephone3
;

// - même opération sur la table Reservation. Pour cette table, il y a deux champs à mettre à jour

sp_rename 'Reservation','Reservation3';

create table Reservation4
(
NoReservation integer not null,
old_NoReservation numeric(18,0) null,

NoPersonne integer null,
old_NoPersonne numeric(18,0) null
)
;

insert into Reservation4
(
NoReservation,
old_NoReservation,

NoPersonne,
old_NoPersonne
)

select
0,
NoReservation,

0,
NoPersonne

From Reservation3
;

// - Mise à jour des champs
// - 5000002560 ---> 5000002560 ---> 0652000005 ---> 065200 ---> 002560 ---> 2560 ---> 12560
// - numeric ---> charactere ---> inversée ---> -5 caracteres ---> inversée ---> entier ---> +10000
// - convert reverse substring reverse convert +

// - des écarts sont pris pour éviter les doublons ex : 500002560 et 1000002560 ---> 12560 et 22560

update telephone4
set ref = old_ref
where old_ref<500000000000000 ;

update telephone4
set ref = 10000+convert(int,reverse(substring(reverse(convert(varchar(20),old_ref)),1,5)))
where old_ref>500000000000000 AND old_ref <600000000000000 ;

update telephone4
set ref = 20000+convert(int,reverse(substring(reverse(convert(varchar(20),old_ref)),1,5)))
where old_ref>1000000000000000 AND old_ref <1100000000000000 ;

update Reservation4
set NoReservation = old_NoReservation
where old_NoReservation<500000000000000 ;

update Reservation4
set NoReservation = 10000+convert(int,reverse(substring(reverse(convert(varchar(20),old_NoReservation)),1,5)))
where old_NoReservation>500000000000000 AND old_NoReservation <600000000000000 ;

update Reservation4
set NoReservation = 20000+convert(int,reverse(substring(reverse(convert(varchar(20),old_NoReservation)),1,5)))
where old_NoReservation>1000000000000000 AND old_NoReservation <1100000000000000 ;

update Reservation4
set NoReservation = 30000+convert(int,reverse(substring(reverse(convert(varchar(20),old_NoReservation)),1,5)))
where old_NoReservation>1500000000000000 AND old_NoReservation <1600000000000000 ;

// - Mise à jour du champs Nopersonne à partir des champs de la table intermediaire. On fait une correspondance exacte
// entre la nouvelle clée et l'ancienne

update Reservation4
set NoPersonne = ref
from dbo.telephone4
where old_NoPersonne = old_ref
;

// - creation de la table telephone finale

create table telephone
(ref integer not null,
entite varchar(50) null,
nom varchar(30) null,
prenom varchar(30) null,
ligne_directe varchar(15) null,
emploi varchar(100) null,
site varchar(30) null,
etage varchar(10) null,
bureau varchar(10) null,
poste varchar(10) null,
fax varchar(15) null,
email varchar(100) null,
insee varchar(25) null,
jours varchar(4) null,
mois varchar(4) null,
annees varchar(5) null,
photo varchar(50) null,
offre_poste_saisi varchar(2) null,
offre_posts_val varchar(2) null,
login varchar(30) null,
code_adres numeric(5,0) null,
sup2 numeric(18,0) null,
mobile varchar(15) null
)
;

insert into telephone
(ref,
entite,
nom,
prenom,
ligne_directe,
emploi,
site,
etage,
bureau,
poste,
fax,
email,
insee,
jours,
mois,
annees,
photo,
offre_poste_saisi,
offre_posts_val,
login,
code_adres,
sup2,
mobile
)

select
telephone4.ref,
telephone3.entite,
telephone3.nom,
telephone3.prenom,
telephone3.ligne_directe,
telephone3.emploi,
telephone3.site,
telephone3.etage,
telephone3.bureau,
telephone3.poste,
telephone3.fax,
telephone3.email,
telephone3.insee,
telephone3.jours,
telephone3.mois,
telephone3.annees,
telephone3.photo,
telephone3.offre_poste_saisi,
telephone3.offre_posts_val,
telephone3.login,
telephone3.code_adres,
telephone3.sup2,
telephone3.mobile

From telephone3, telephone4

where telephone4.old_ref = telephone3.ref
;

alter table telephone
add constraint ref primary key nonclustered
(ref) ;

// - creation de la table telephone finale

create table Reservation
(NoReservation integer not null,
NoSalle char(4) null,
DateDebut smalldatetime null,
DateFin smalldatetime null,
NoPersonne integer null,
Theme varchar(200) null,
Nbparticip varchar(4) null,
Nbjours varchar(4) null,
Projecteur varchar(3) null,
Television varchar(3) null,
Retropro varchar(3) null,
Paperboard varchar(3) null,
Camescope varchar(3) null,
Informatique varchar(3) null,
Nbinforma varchar(4) null,
Demande varchar(200) null,
Validation varchar(15) null,
DateEnr smalldatetime null,
dodp varchar(10) null,
Nbintervenant varchar(4) null,
dispo varchar(10) null,
eau varchar(3) null,
white_paper varchar(3) null,
magnetoscope varchar(3) null,
markerpb varchar(3) null,
markerwp varchar(3) null,
fblanches varchar(3) null,
ksept varchar(3) null,
crayons varchar(3) null,
ecran varchar(3) null,
rallonge varchar(3) null,
reseau varchar(3) null,
internet varchar(3) null,
imprimante varchar(3) null,
application varchar(3) null,
cafet varchar(3) null,
pot varchar(3) null,
remarque_tables varchar(200) null,
remarques_info varchar(200) null,
remarque_materiel varchar(200) null) ;

insert into Reservation
(NoReservation,
NoSalle,
DateDebut,
DateFin,
NoPersonne,
Theme,
Nbparticip,
Nbjours,
Projecteur,
Television,
Retropro,
Paperboard,
Camescope,
Informatique,
Nbinforma,
Demande,
Validation,
DateEnr,
dodp,
Nbintervenant,
dispo,
eau,
white_paper,
magnetoscope,
markerpb,
markerwp,
fblanches,
ksept,
crayons,
ecran,
rallonge,
reseau,
internet,
imprimante,
application,
cafet,
pot,
remarque_tables,
remarques_info,
remarque_materiel)

select
Reservation4.NoReservation,
Reservation3.NoSalle,
Reservation3.DateDebut,
Reservation3.DateFin,
Reservation4.NoPersonne,
Reservation3.Theme,
Reservation3.Nbparticip,
Reservation3.Nbjours,
Reservation3.Projecteur,
Reservation3.Television,
Reservation3.Retropro,
Reservation3.Paperboard,
Reservation3.Camescope,
Reservation3.Informatique,
Reservation3.Nbinforma,
Reservation3.Demande,
Reservation3.Validation,
Reservation3.DateEnr,
Reservation3.dodp,
Reservation3.Nbintervenant,
Reservation3.dispo,
Reservation3.eau,
Reservation3.white_paper,
Reservation3.magnetoscope,
Reservation3.markerpb,
Reservation3.markerwp,
Reservation3.fblanches,
Reservation3.ksept,
Reservation3.crayons,
Reservation3.ecran,
Reservation3.rallonge,
Reservation3.reseau,
Reservation3.internet,
Reservation3.imprimante,
Reservation3.application,
Reservation3.cafet,
Reservation3.pot,
Reservation3.remarque_tables,
Reservation3.remarques_info,
Reservation3.remarque_materiel

From Reservation4, Reservation3
where Reservation4.old_NoReservation = Reservation3.NoReservation
;

update Reservation
set NoPersonne = 20978
where NoPersonne = 0
;

alter table Reservation
add constraint Noreserv_ID primary key nonclustered
(NoReservation) ;