DECLARE @ID NVARCHAR(128), @TextChyby NVARCHAR(255), @Error INT, @ROWCOUNT INT , @ProblemovyPrikaz NVARCHAR(MAX) /*20200710*/ DECLARE CUR CURSOR LOCAL FAST_FORWARD FOR SELECT ID FROM TabRozkopirovaniIDproCursor ORDER BY ID OPEN CUR WHILE 1 = 1 BEGIN FETCH CUR INTO @ID IF @@FETCH_STATUS <> 0 BREAK BEGIN TRY IF ((SELECT (SELECT DISTINCT c1.CisloOrg FROM IZ_1_Kopie..TabCisOrg AS c1 JOIN TabCisOrg AS z1 ON (c1.ICO = z1.ICO OR (c1.ICO IS NULL AND z1.ICO IS NULL)) WHERE z1.CisloOrg = Z.NadrizenaOrg) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT NadrizenaOrg FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.ISOKod FROM IZ_1_Kopie..TabZeme AS c1 JOIN TabZeme AS z1 ON (c1.ISOKod = z1.ISOKod) WHERE z1.ISOKod = Z.IdZeme) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT IdZeme FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.Cislo FROM IZ_1_Kopie..TabRegion AS c1 JOIN TabRegion AS z1 ON (c1.Cislo = z1.Cislo) WHERE z1.Cislo = Z.Region) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT Region FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabDruhCinnosti AS c1 JOIN TabDruhCinnosti AS z1 ON (c1.Nazev = z1.Nazev) WHERE z1.ID = Z.DruhCinnosti) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT DruhCinnosti FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.FormaUhrady FROM IZ_1_Kopie..TabFormaUhrady AS c1 JOIN TabFormaUhrady AS z1 ON (c1.FormaUhrady = z1.FormaUhrady) WHERE z1.FormaUhrady = Z.FormaUhrady) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT FormaUhrady FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabCisZam AS c1 JOIN TabCisZam AS z1 ON (c1.RodneCislo = z1.RodneCislo) WHERE z1.ID = Z.OdpOs) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT OdpOs FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.Kod FROM IZ_1_Kopie..TabKodMen AS c1 JOIN TabKodMen AS z1 ON (c1.Kod = z1.Kod) WHERE z1.Kod = Z.Mena) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT Mena FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabBankSpojeni AS c1 JOIN TabBankSpojeni AS z1 ON (c1.CisloUctu = z1.CisloUctu) WHERE z1.ID = Z.IDBankSpojeni) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT IDBankSpojeni FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabBankSpojeni AS c1 JOIN TabBankSpojeni AS z1 ON (c1.CisloUctu = z1.CisloUctu) WHERE z1.ID = Z.IDBankSpojPlatak) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT IDBankSpojPlatak FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.Jazyk FROM IZ_1_Kopie..TabJazyky AS c1 JOIN TabJazyky AS z1 ON (c1.Jazyk = z1.Jazyk) WHERE z1.Jazyk = Z.Jazyk) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT Jazyk FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabKategOrg AS c1 JOIN TabKategOrg AS z1 ON (c1.Nazev = z1.Nazev) WHERE z1.ID = Z.IDKateg) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT IDKateg FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) OR ((SELECT (SELECT DISTINCT c1.FormaDopravy FROM IZ_1_Kopie..TabFormaDopravy AS c1 JOIN TabFormaDopravy AS z1 ON (c1.FormaDopravy = z1.FormaDopravy OR (c1.FormaDopravy IS NULL AND z1.FormaDopravy IS NULL)) WHERE z1.FormaDopravy = Z.FormaDopravy) FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NULL AND (SELECT FormaDopravy FROM TabCisOrg AS Z WHERE Z.Id = @ID ) IS NOT NULL ) BEGIN SELECT @TextChyby = 'Nepodarilo se najit navazne udaje nektereho ze sloupcu: NadrizenaOrg, IdZeme, Region, DruhCinnosti, FormaUhrady, OdpOs, Mena, IDBankSpojeni, IDBankSpojPlatak, Jazyk, IDKateg, FormaDopravy' EXEC hp_RozkopLog 5041/*@ErrID*/, 0 /*@OK*/, 1, @TextChyby /*@TextChyby*/ CONTINUE END INSERT INTO IZ_1_Kopie..TabCisOrg ( CisloOrg, NadrizenaOrg, Nazev, DruhyNazev, Misto, IdZeme, Region, Ulice, OrCislo, PopCislo, PSC, PoBox , Kontakt, DIC, DICsk, LhutaSplatnosti, Stav, PravniForma, DruhCinnosti, ICO, Sleva, OdHodnoty, Poznamka, FormaUhrady, JeOdberatel , JeDodavatel, VernostniProgram, OdpOs, Upozorneni, CisloOrgDos, Mena, DatPorizeni, Autor, DatZmeny, Zmenil, Fakturacni, MU, Prijemce , UdajOZapisuDoObchRej, IDBankSpojeni, IDBankSpojPlatak, CarovyKodEAN, PostAddress, Kredit, Saldo, UhrazenoPredSpl1, UhrazenoPredSpl2 , UhrazenoPredSpl3, UhrazenoPredSpl4, UhrazenoPredSpl5, UhrazenoPredSpl6, UhrazenoPredSpl0, UhrazenoPoSpl1, UhrazenoPoSpl2, UhrazenoPoSpl3 , UhrazenoPoSpl4, UhrazenoPoSpl5, UhrazenoPoSpl6, UhrazenoPoSpl0, NeuhrazenoPredSpl1, NeuhrazenoPredSpl2, NeuhrazenoPredSpl3 , NeuhrazenoPredSpl4, NeuhrazenoPredSpl5, NeuhrazenoPredSpl6, NeuhrazenoPredSpl0, NeuhrazenoPoSpl1, NeuhrazenoPoSpl2, NeuhrazenoPoSpl3 , NeuhrazenoPoSpl4, NeuhrazenoPoSpl5, NeuhrazenoPoSpl6, NeuhrazenoPoSpl0, FaSumaCelkem, PozastavenoCelkem, FaAktualizovano , PlneniBezDPH, Jazyk, DatumNeupominani, TIN, EORI, EvCisDanovySklad, SlevaSozNa, SlevaSkupZbo, SlevaKmenZbo, SlevaStavSkladu , SlevaZbozi, SlevaOrg, Logo, KorekceSplatnoAuto, KorekceSplatnoUziv, KorekceSplatnoUzivD, NapocetProPT, RezimPenalizace , Penale, DruhDopravy, DodaciPodminky, Partner, PlatceDPH, NazevOkresu, NazevCastiObce, MestskaCast, KodAdrMista, NespolehPlatce , AktZWebuNespolehPlatce, AktZWebuZverejBankUcty, DatZverejNespolehPlatce, DatPoslOverNespolehPlatceSys, DatPoslOverNespolehPlatceUziv , ZajisteniDPHCisloFU, ZajisteniDPHJinyDuvodRuceni, ZajisteniDPHJinyDuvodRuceniDatZverej, PartnerICO, LhutaSplatnostiDodavatel , KonsolidaceStatu, Legislativa, DICDPHVystup, GPSZemepisnaSirka, GPSZemepisnaDelka, JePartner, EmailSouhlas, IDKateg, Obrat, PocetZam , PZ2Vstup, PZ2Vystup, PZ2PoradiVstup, PZ2PoradiVystup, DatumCasPoslSynchr, OmezeniZpracOU, JeNovaVetaEditor, FormaDopravy , SKDPHPlatceNaZruseni, Agent, AktOrgDleRegistru, LimitPlatHotovKontrola, HraniceMarze, PouzitMarzeOdDo ) SELECT z.CisloOrg, (SELECT DISTINCT c1.CisloOrg FROM IZ_1_Kopie..TabCisOrg AS c1 JOIN TabCisOrg AS z1 ON (c1.ICO = z1.ICO OR (c1.ICO IS NULL AND z1.ICO IS NULL)) WHERE z1.CisloOrg = Z.NadrizenaOrg), z.Nazev, z.DruhyNazev, z.Misto , (SELECT DISTINCT c1.ISOKod FROM IZ_1_Kopie..TabZeme AS c1 JOIN TabZeme AS z1 ON (c1.ISOKod = z1.ISOKod) WHERE z1.ISOKod = Z.IdZeme) , (SELECT DISTINCT c1.Cislo FROM IZ_1_Kopie..TabRegion AS c1 JOIN TabRegion AS z1 ON (c1.Cislo = z1.Cislo) WHERE z1.Cislo = Z.Region) , z.Ulice, z.OrCislo, z.PopCislo, z.PSC, z.PoBox, z.Kontakt, z.DIC, z.DICsk, z.LhutaSplatnosti, z.Stav, z.PravniForma , (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabDruhCinnosti AS c1 JOIN TabDruhCinnosti AS z1 ON (c1.Nazev = z1.Nazev) WHERE z1.ID = Z.DruhCinnosti), z.ICO, z.Sleva, z.OdHodnoty, z.Poznamka , (SELECT DISTINCT c1.FormaUhrady FROM IZ_1_Kopie..TabFormaUhrady AS c1 JOIN TabFormaUhrady AS z1 ON (c1.FormaUhrady = z1.FormaUhrady) WHERE z1.FormaUhrady = Z.FormaUhrady), z.JeOdberatel, z.JeDodavatel, z.VernostniProgram , (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabCisZam AS c1 JOIN TabCisZam AS z1 ON (c1.RodneCislo = z1.RodneCislo) WHERE z1.ID = Z.OdpOs) , z.Upozorneni, z.CisloOrgDos, (SELECT DISTINCT c1.Kod FROM IZ_1_Kopie..TabKodMen AS c1 JOIN TabKodMen AS z1 ON (c1.Kod = z1.Kod) WHERE z1.Kod = Z.Mena) , z.DatPorizeni, z.Autor, z.DatZmeny, z.Zmenil, z.Fakturacni, z.MU, z.Prijemce, z.UdajOZapisuDoObchRej , (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabBankSpojeni AS c1 JOIN TabBankSpojeni AS z1 ON (c1.CisloUctu = z1.CisloUctu) WHERE z1.ID = Z.IDBankSpojeni), (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabBankSpojeni AS c1 JOIN TabBankSpojeni AS z1 ON (c1.CisloUctu = z1.CisloUctu) WHERE z1.ID = Z.IDBankSpojPlatak) , z.CarovyKodEAN, z.PostAddress, z.Kredit, z.Saldo, z.UhrazenoPredSpl1, z.UhrazenoPredSpl2, z.UhrazenoPredSpl3, z.UhrazenoPredSpl4 , z.UhrazenoPredSpl5, z.UhrazenoPredSpl6, z.UhrazenoPredSpl0, z.UhrazenoPoSpl1, z.UhrazenoPoSpl2, z.UhrazenoPoSpl3, z.UhrazenoPoSpl4 , z.UhrazenoPoSpl5, z.UhrazenoPoSpl6, z.UhrazenoPoSpl0, z.NeuhrazenoPredSpl1, z.NeuhrazenoPredSpl2, z.NeuhrazenoPredSpl3, z.NeuhrazenoPredSpl4 , z.NeuhrazenoPredSpl5, z.NeuhrazenoPredSpl6, z.NeuhrazenoPredSpl0, z.NeuhrazenoPoSpl1, z.NeuhrazenoPoSpl2, z.NeuhrazenoPoSpl3, z.NeuhrazenoPoSpl4 , z.NeuhrazenoPoSpl5, z.NeuhrazenoPoSpl6, z.NeuhrazenoPoSpl0, z.FaSumaCelkem, z.PozastavenoCelkem, z.FaAktualizovano, z.PlneniBezDPH , (SELECT DISTINCT c1.Jazyk FROM IZ_1_Kopie..TabJazyky AS c1 JOIN TabJazyky AS z1 ON (c1.Jazyk = z1.Jazyk) WHERE z1.Jazyk = Z.Jazyk) , z.DatumNeupominani, z.TIN, z.EORI, z.EvCisDanovySklad, z.SlevaSozNa, z.SlevaSkupZbo, z.SlevaKmenZbo, z.SlevaStavSkladu, z.SlevaZbozi , z.SlevaOrg, z.Logo, z.KorekceSplatnoAuto, z.KorekceSplatnoUziv, z.KorekceSplatnoUzivD, z.NapocetProPT, z.RezimPenalizace, z.Penale , z.DruhDopravy, z.DodaciPodminky, z.Partner, z.PlatceDPH, z.NazevOkresu, z.NazevCastiObce, z.MestskaCast, z.KodAdrMista, z.NespolehPlatce , z.AktZWebuNespolehPlatce, z.AktZWebuZverejBankUcty, z.DatZverejNespolehPlatce, z.DatPoslOverNespolehPlatceSys, z.DatPoslOverNespolehPlatceUziv , z.ZajisteniDPHCisloFU, z.ZajisteniDPHJinyDuvodRuceni, z.ZajisteniDPHJinyDuvodRuceniDatZverej, z.PartnerICO, z.LhutaSplatnostiDodavatel , z.KonsolidaceStatu, z.Legislativa, z.DICDPHVystup, z.GPSZemepisnaSirka, z.GPSZemepisnaDelka, z.JePartner, z.EmailSouhlas , (SELECT DISTINCT c1.ID FROM IZ_1_Kopie..TabKategOrg AS c1 JOIN TabKategOrg AS z1 ON (c1.Nazev = z1.Nazev) WHERE z1.ID = Z.IDKateg) , z.Obrat, z.PocetZam, z.PZ2Vstup, z.PZ2Vystup, z.PZ2PoradiVstup, z.PZ2PoradiVystup, z.DatumCasPoslSynchr, z.OmezeniZpracOU, z.JeNovaVetaEditor , (SELECT DISTINCT c1.FormaDopravy FROM IZ_1_Kopie..TabFormaDopravy AS c1 JOIN TabFormaDopravy AS z1 ON (c1.FormaDopravy = z1.FormaDopravy OR (c1.FormaDopravy IS NULL AND z1.FormaDopravy IS NULL)) WHERE z1.FormaDopravy = Z.FormaDopravy), z.SKDPHPlatceNaZruseni, z.Agent, z.AktOrgDleRegistru, z.LimitPlatHotovKontrola, z.HraniceMarze, z.PouzitMarzeOdDo FROM TabCisOrg AS Z LEFT JOIN IZ_1_Kopie..TabCisOrg AS C ON (c.ICO = z.ICO OR (c.ICO IS NULL AND z.ICO IS NULL ) ) WHERE 1 = 1 AND C.ICO IS NULL AND Z.Id = @ID SELECT @ROWCOUNT = @@ROWCOUNT EXEC hp_RozkopLog 5041/*@ErrID*/, 1 /*@OK*/, @ROWCOUNT, NULL /*@TextChyby*/ END TRY BEGIN CATCH SELECT @TextChyby = LEFT(ERROR_MESSAGE(), 255) SELECT @ProblemovyPrikaz = ProblemovyPrikaz FROM #ProblemovyPrikaz /*20200710*/ EXEC hp_RozkopLog 5041/*@ErrID*/, 0 /*@OK*/, 1, @TextChyby /*@TextChyby*/, @ProblemovyPrikaz /*20200710*/ END CATCH END CLOSE CUR DEALLOCATE CUR