SET IDENTITY_INSERT asurnet.dbo.PERSONAS ON;

INSERT INTO asurnet.dbo.PERSONAS
(PRS_Id, PRS_Nombre,PRS_Apellido1,PRS_Apellido2,PRS_Direccion,
PRS_Localidad,PRS_CP,PRS_TelfPal,PRS_TelfSec,PRS_Fax,PRS_Sexo,PRS_Nacimiento,PRS_FechaCarnet,
PRS_CaducidadCarnet,PRS_TipoPersona,PRS_FC,PRS_FM,USR_Id_FC,USR_Id_FM,USR_Id_FC_Tesis,USR_Id_FM_Tesis,PRS_Visible,PRS_Estado,
PRS_Profesion,PRS_TelfMovil,PRS_Email,PRS_NombreBanco,PRS_NumCuenta,CIA_Id,PRS_Titular,CPT_Id,
PRS_NIF,PRS_CCC)
SELECT A.PRS_Id, A.PRS_Nombre,A.PRS_Apellido1,A.PRS_Apellido2,A.PRS_Direccion,
       A.PRS_Localidad,A.PRS_CP,A.PRS_TelfPal,A.PRS_TelfSec,A.PRS_Fax,A.PRS_Sexo,A.PRS_Nacimiento,A.PRS_FechaCarnet,
       A.PRS_CaducidadCarnet,A.PRS_TipoPersona,A.PRS_FC,A.PRS_FM,A.USR_Id_FC,A.USR_Id_FM,C.IDTSS,D.IDTSS,A.PRS_Visible,A.PRS_Estado,
       A.PRS_Profesion,A.PRS_TelfMovil,A.PRS_Email,A.PRS_NombreBanco,A.PRS_NumCuenta,A.CIA_Id,A.PRS_Titular,A.CPT_Id,
       A.PRS_NIF,A.PRS_CCC
FROM vsegbas.dbo.PERSONAS A
LEFT JOIN tssseg0001.dbo.IDTraspasos C
ON A.USR_Id_FC = C.IDTras AND C.TableID = 'Usuarios'
LEFT JOIN tssseg0001.dbo.IDTraspasos D
ON A.USR_Id_FM = D.IDTras AND D.TableID = 'Usuarios';

SET IDENTITY_INSERT asurnet.dbo.PERSONAS OFF;
SET IDENTITY_INSERT asurnet.dbo.PERITOS ON;
INSERT INTO asurnet.dbo.PERITOS
(SinNumID, SinSer,PRS_Id,PRT_Peritaje,PRT_Importe,
 PRT_Importe2,PRT_Importe3,PRT_Peritaje2,PRT_Peritaje3,PRT_Lugar,PRT_Observaciones,PRT_ID)
SELECT CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'), CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Id,PRT_Peritaje,PRT_Importe,
       PRT_Importe2,PRT_Importe3,PRT_Peritaje2,PRT_Peritaje3,PRT_Lugar,PRT_Observaciones,PRT_ID
            FROM vsegbas.dbo.PERITOS A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras;
SET IDENTITY_INSERT asurnet.dbo.PERITOS OFF;
SET IDENTITY_INSERT asurnet.dbo.LESIONADOS ON;
INSERT INTO asurnet.dbo.LESIONADOS
(LES_Id, LES_Tipo,LES_ECivil,LES_Profesion,LES_Centro,
 LES_TelfCentro,LES_FaxCentro,LES_Departamento,LES_Habitacion,LES_Lesiones,LES_Secuelas,LES_Observaciones,
 LES_NIFCentro,SinNumID,SinSer,SinPerNum,SIN_MyId)
SELECT LES_Id, LES_Tipo,LES_ECivil,LES_Profesion,LES_Centro,
       LES_TelfCentro,LES_FaxCentro,LES_Departamento,LES_Habitacion,LES_Lesiones,LES_Secuelas,LES_Observaciones,
       LES_NIFCentro,CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'),CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Lesionado,SIN_Id
FROM vsegbas.dbo.LESIONADOS A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras
WHERE A.PRS_Lesionado IS NOT NULL;
SET IDENTITY_INSERT asurnet.dbo.LESIONADOS OFF;
SET IDENTITY_INSERT asurnet.dbo.PROFESIONES ON;
INSERT INTO asurnet.dbo.PROFESIONES
(PRN_Id, PRN_Descripcion,PRN_CODIGO)
SELECT PRN_Id, PRN_Descripcion,PRN_CODIGO
FROM vsegbas.dbo.PROFESIONES A;
SET IDENTITY_INSERT asurnet.dbo.PROFESIONES OFF;

INSERT INTO asurnet.dbo.TRAMITADOR
(SinNumID, SinSer,PRS_Id)
SELECT CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'), CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Id
FROM vsegbas.dbo.TRAMITADOR A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras;


INSERT INTO asurnet.dbo.Abogados
(SinNumID, SinSer,PRS_Id)
SELECT CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'), CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Id
FROM vsegbas.dbo.Abogados A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras;

INSERT INTO asurnet.dbo.PROFESIONALES
(SinNumID, SinSer,PRS_Id)
SELECT CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'), CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Id
FROM vsegbas.dbo.PROFESIONALES A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras;

SET IDENTITY_INSERT asurnet.dbo.CONTRARIOS ON;
INSERT INTO asurnet.dbo.CONTRARIOS
(CTR_Id, PRS_Contrario,PRS_Taller,PRS_Conductor,CTR_Modelo,CTR_Matricula,CTR_Danos,CTR_Observaciones,
 CTR_NombreCia,CTR_TelfCia,CTR_Poliza,CTR_RefCia,CTR_Tramitador,CTR_Circunstancias,SinNumID, SinSer,SinConNum)
SELECT CTR_Id, PRS_Contrario,PRS_Taller,PRS_Conductor,CTR_Modelo,CTR_Matricula,CTR_Danos,CTR_Observaciones,
       CTR_NombreCia,CTR_TelfCia,CTR_Poliza,CTR_RefCia,CTR_Tramitador,CTR_Circunstancias,CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'), CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Contrario
FROM vsegbas.dbo.CONTRARIOS A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras AND A.PRS_Contrario IS NOT NULL;
SET IDENTITY_INSERT asurnet.dbo.CONTRARIOS OFF;

INSERT INTO asurnet.dbo.TESTIGOS
(SinNumID, SinSer,PRS_Id)
SELECT CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)'), CAST('<t>' + REPLACE(C.IDTSS, '/','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)'),PRS_Id
FROM vsegbas.dbo.TESTIGOS A
         INNER JOIN (
    SELECT * FROM tssseg0001.dbo.IDTraspasos
    WHERE TableID = 'Siniestros'
) C ON A.SIN_Id = C.IdTras;