I encountered errors while importing tables from an Access. The only tbales
that errored had date/time fields and it seems SQL didn't like them. The tab
les work just fine in Access so I'm surprised SQL didn't like them. Anyone s
ee this before?Alan,
Most likely cause: MS Access supports dates as early as Jan 1, 100, while
SQL Server only supports dates back to Jan 1, 1753. You probably have a date
prior to Jan 1, 1753. Before you dismiss this idea, keep in mind that a
simple data entry error can morph Jul 15, 1999 to Jul 15, 199 :-)
Use an Access query to locate dates earlier than Jan 1, 1753.
Chief Tenaya
"Alan Fisher" <anonymous@.discussions.microsoft.com> wrote in message
news:838D8D52-BB99-416A-A4D2-4781DCE7040F@.microsoft.com...
> I encountered errors while importing tables from an Access. The only
tbales that errored had date/time fields and it seems SQL didn't like them.
The tables work just fine in Access so I'm surprised SQL didn't like them.
Anyone see this before?|||On Tue, 23 Mar 2004 16:01:06 -0800, "Alan Fisher"
<anonymous@.discussions.microsoft.com> wrote:
Different versions of Access have different levels of support for
upsizing. Try with Access XP or 2003.
When push comes to shov, it's not so hard to build those few tables
yourself, and write append queries to copy over the data.
-Tom.
>I encountered errors while importing tables from an Access. The only tbales that er
rored had date/time fields and it seems SQL didn't like them. The tables work just f
ine in Access so I'm surprised SQL didn't like them. Anyone see this before?
Showing posts with label encountered. Show all posts
Showing posts with label encountered. Show all posts
Wednesday, March 21, 2012
Friday, February 24, 2012
import 1 mln records = QA is hung up
Hi
I encountered a problem when I'm trying to import data from a flat table to
several tables of the same databases.
There are about 1 mln records inside the flat table called XX_ZRODLO. The
stored procedure for import seems to work and import data but after about
15000 it stops and it seems as though QA is hung up.
After restart of SQL and shutdown QA - the procedure for import can import
another about 30000 records and then the same agian QA is dead ...
Where could be the problem?
This is the procedure to import data (1 mln records) from table XX_ZRODLO to
several tables of databases according to its structure.
CREATE procedure XX_IMPORT
as
--##### SEKCJA DEKLARACJI
declare @.nazwa nvarchar(255)
declare @.id int
declare @.id_woj int
declare @.id_miasto int
declare @.ulica nvarchar(255)
declare @.telefon nvarchar(255)
declare @.id_branza int
declare @.www nvarchar(255)
declare @.mail nvarchar(255)
--nowo dodane
declare @.kod nvarchar(255)
declare @.numer_pos nvarchar(255)
declare @.kierunkowy nvarchar(255)
declare @.tb_firma_identity int
declare @.tb_adres_identity int
--koniec
declare @.id_firma int
declare @.id_adres int
set @.nazwa = ''
set @.id = 0
set @.id_woj = 0
set @.id_miasto=0
set @.ulica=''
set @.telefon=''
set @.kierunkowy=''
set @.id_branza=0
set @.www=''
set @.mail=''
--nowo dodane
set @.kod = ''
set @.numer_pos =''
set @.kierunkowy =''
--koniec
-- ####### KONIEC SEKCJI DEKLARACJI
--######## POCZATEK
begin
declare tb cursor for (select
nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
from xx_zrodlo where id_portal is null)
open tb
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
while @.@.fetch_status = 0
begin
IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
BEGIN
INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
select @.@.Identity
set @.tb_firma_identity = @.@.Identity
--select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
VALUES (@.tb_firma_identity,@.id_branza,30000,1)
INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
(@.ulica,@.numer_pos,@.kod,@.id_miasto)
select @.@.Identity
set @.tb_adres_identity = @.@.Identity
--select @.id_adres = max(idTB_ADRES) from TB_ADRES
INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
(@.tb_firma_identity,@.tb_adres_identity,1)
--przypadek telefonu komórkowego
--IF substring(@.telefon,1,1) = 0
IF @.kierunkowy = 0
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,5,@.tb_adres_identity,1)
END
--przypadek telefonu stacjonarnego
--IF substring(@.telefon,1,1) != 0
IF @.kierunkowy <>0 AND @.kierunkowy is not null
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,2,@.tb_adres_identity,1)
END
--przypadek adresu www
IF len(@.www) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.www,3,@.tb_adres_identity,1)
END
--przypadek adresu email
IF len(@.mail) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.mail,4,@.tb_adres_identity,1)
END
END
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
END
end
close tb
deallocate tb
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi
It seems that the id column is a natural key for this and therefore you
could do this using set based operations and not a cursor. This would speed
up your process.
John
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23Qw81E1zGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi
> I encountered a problem when I'm trying to import data from a flat table
> to several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO
> to several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1)
> --przypadek telefonu komórkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>|||Dariusz Tomon wrote:
> Hi
> I encountered a problem when I'm trying to import data from a flat table to
> several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO to
> several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1)
> --przypadek telefonu komórkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
Launch another QA session and look at the sysprocesses table - is there
blocking? Does the spid that your import is using show a waittype? Are
you see a delay due to autogrow?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I encountered a problem when I'm trying to import data from a flat table to
several tables of the same databases.
There are about 1 mln records inside the flat table called XX_ZRODLO. The
stored procedure for import seems to work and import data but after about
15000 it stops and it seems as though QA is hung up.
After restart of SQL and shutdown QA - the procedure for import can import
another about 30000 records and then the same agian QA is dead ...
Where could be the problem?
This is the procedure to import data (1 mln records) from table XX_ZRODLO to
several tables of databases according to its structure.
CREATE procedure XX_IMPORT
as
--##### SEKCJA DEKLARACJI
declare @.nazwa nvarchar(255)
declare @.id int
declare @.id_woj int
declare @.id_miasto int
declare @.ulica nvarchar(255)
declare @.telefon nvarchar(255)
declare @.id_branza int
declare @.www nvarchar(255)
declare @.mail nvarchar(255)
--nowo dodane
declare @.kod nvarchar(255)
declare @.numer_pos nvarchar(255)
declare @.kierunkowy nvarchar(255)
declare @.tb_firma_identity int
declare @.tb_adres_identity int
--koniec
declare @.id_firma int
declare @.id_adres int
set @.nazwa = ''
set @.id = 0
set @.id_woj = 0
set @.id_miasto=0
set @.ulica=''
set @.telefon=''
set @.kierunkowy=''
set @.id_branza=0
set @.www=''
set @.mail=''
--nowo dodane
set @.kod = ''
set @.numer_pos =''
set @.kierunkowy =''
--koniec
-- ####### KONIEC SEKCJI DEKLARACJI
--######## POCZATEK
begin
declare tb cursor for (select
nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
from xx_zrodlo where id_portal is null)
open tb
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
while @.@.fetch_status = 0
begin
IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
BEGIN
INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
select @.@.Identity
set @.tb_firma_identity = @.@.Identity
--select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
VALUES (@.tb_firma_identity,@.id_branza,30000,1)
INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
(@.ulica,@.numer_pos,@.kod,@.id_miasto)
select @.@.Identity
set @.tb_adres_identity = @.@.Identity
--select @.id_adres = max(idTB_ADRES) from TB_ADRES
INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
(@.tb_firma_identity,@.tb_adres_identity,1)
--przypadek telefonu komórkowego
--IF substring(@.telefon,1,1) = 0
IF @.kierunkowy = 0
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,5,@.tb_adres_identity,1)
END
--przypadek telefonu stacjonarnego
--IF substring(@.telefon,1,1) != 0
IF @.kierunkowy <>0 AND @.kierunkowy is not null
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,2,@.tb_adres_identity,1)
END
--przypadek adresu www
IF len(@.www) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.www,3,@.tb_adres_identity,1)
END
--przypadek adresu email
IF len(@.mail) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.mail,4,@.tb_adres_identity,1)
END
END
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
END
end
close tb
deallocate tb
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi
It seems that the id column is a natural key for this and therefore you
could do this using set based operations and not a cursor. This would speed
up your process.
John
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23Qw81E1zGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi
> I encountered a problem when I'm trying to import data from a flat table
> to several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO
> to several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1)
> --przypadek telefonu komórkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>|||Dariusz Tomon wrote:
> Hi
> I encountered a problem when I'm trying to import data from a flat table to
> several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO to
> several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1)
> --przypadek telefonu komórkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.telefon,@.id_branza,@.www,@.mail,@.kod,@.numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
Launch another QA session and look at the sysprocesses table - is there
blocking? Does the spid that your import is using show a waittype? Are
you see a delay due to autogrow?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
import 1 mln records = QA is hung up
Hi
I encountered a problem when I'm trying to import data from a flat table to
several tables of the same databases.
There are about 1 mln records inside the flat table called XX_ZRODLO. The
stored procedure for import seems to work and import data but after about
15000 it stops and it seems as though QA is hung up.
After restart of SQL and shutdown QA - the procedure for import can import
another about 30000 records and then the same agian QA is dead ...
Where could be the problem?
This is the procedure to import data (1 mln records) from table XX_ZRODLO to
several tables of databases according to its structure.
CREATE procedure XX_IMPORT
as
--##### SEKCJA DEKLARACJI
declare @.nazwa nvarchar(255)
declare @.id int
declare @.id_woj int
declare @.id_miasto int
declare @.ulica nvarchar(255)
declare @.telefon nvarchar(255)
declare @.id_branza int
declare @.www nvarchar(255)
declare @.mail nvarchar(255)
--nowo dodane
declare @.kod nvarchar(255)
declare @.numer_pos nvarchar(255)
declare @.kierunkowy nvarchar(255)
declare @.tb_firma_identity int
declare @.tb_adres_identity int
--koniec
declare @.id_firma int
declare @.id_adres int
set @.nazwa = ''
set @.id = 0
set @.id_woj = 0
set @.id_miasto=0
set @.ulica=''
set @.telefon=''
set @.kierunkowy=''
set @.id_branza=0
set @.www=''
set @.mail=''
--nowo dodane
set @.kod = ''
set @.numer_pos =''
set @.kierunkowy =''
--koniec
-- ####### KONIEC SEKCJI DEKLARACJI
--######## POCZATEK
begin
declare tb cursor for (select
nazwa,id,id_woj,id_miasto,ulica,telefon,
id_branza,www,mail,kod,numer_pos,kie
runkowy
from xx_zrodlo where id_portal is null)
open tb
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.nu
mer_pos,@.kierunkowy
while @.@.fetch_status = 0
begin
IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
BEGIN
INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
select @.@.Identity
set @.tb_firma_identity = @.@.Identity
--select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
VALUES (@.tb_firma_identity,@.id_branza,30000,1)
INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
(@.ulica,@.numer_pos,@.kod,@.id_miasto)
select @.@.Identity
set @.tb_adres_identity = @.@.Identity
--select @.id_adres = max(idTB_ADRES) from TB_ADRES
INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
(@.tb_firma_identity,@.tb_adres_identity,1
)
--przypadek telefonu komrkowego
--IF substring(@.telefon,1,1) = 0
IF @.kierunkowy = 0
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,5,@.tb_adres_identity,1)
END
--przypadek telefonu stacjonarnego
--IF substring(@.telefon,1,1) != 0
IF @.kierunkowy <>0 AND @.kierunkowy is not null
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,2,@.tb_adres_identity,1)
END
--przypadek adresu www
IF len(@.www) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.www,3,@.tb_adres_identity,1)
END
--przypadek adresu email
IF len(@.mail) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.mail,4,@.tb_adres_identity,1)
END
END
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.nu
mer_pos,@.kierunkowy
END
end
close tb
deallocate tb
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi
It seems that the id column is a natural key for this and therefore you
could do this using set based operations and not a cursor. This would speed
up your process.
John
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23Qw81E1zGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi
> I encountered a problem when I'm trying to import data from a flat table
> to several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO
> to several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,
id_branza,www,mail,kod,numer_pos,k
ierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1
)
> --przypadek telefonu komrkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>|||Dariusz Tomon wrote:
> Hi
> I encountered a problem when I'm trying to import data from a flat table t
o
> several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO
to
> several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,
id_branza,www,mail,kod,numer_pos,k
ierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1
)
> --przypadek telefonu komrkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
Launch another QA session and look at the sysprocesses table - is there
blocking? Does the spid that your import is using show a waittype? Are
you see a delay due to autogrow?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I encountered a problem when I'm trying to import data from a flat table to
several tables of the same databases.
There are about 1 mln records inside the flat table called XX_ZRODLO. The
stored procedure for import seems to work and import data but after about
15000 it stops and it seems as though QA is hung up.
After restart of SQL and shutdown QA - the procedure for import can import
another about 30000 records and then the same agian QA is dead ...
Where could be the problem?
This is the procedure to import data (1 mln records) from table XX_ZRODLO to
several tables of databases according to its structure.
CREATE procedure XX_IMPORT
as
--##### SEKCJA DEKLARACJI
declare @.nazwa nvarchar(255)
declare @.id int
declare @.id_woj int
declare @.id_miasto int
declare @.ulica nvarchar(255)
declare @.telefon nvarchar(255)
declare @.id_branza int
declare @.www nvarchar(255)
declare @.mail nvarchar(255)
--nowo dodane
declare @.kod nvarchar(255)
declare @.numer_pos nvarchar(255)
declare @.kierunkowy nvarchar(255)
declare @.tb_firma_identity int
declare @.tb_adres_identity int
--koniec
declare @.id_firma int
declare @.id_adres int
set @.nazwa = ''
set @.id = 0
set @.id_woj = 0
set @.id_miasto=0
set @.ulica=''
set @.telefon=''
set @.kierunkowy=''
set @.id_branza=0
set @.www=''
set @.mail=''
--nowo dodane
set @.kod = ''
set @.numer_pos =''
set @.kierunkowy =''
--koniec
-- ####### KONIEC SEKCJI DEKLARACJI
--######## POCZATEK
begin
declare tb cursor for (select
nazwa,id,id_woj,id_miasto,ulica,telefon,
id_branza,www,mail,kod,numer_pos,kie
runkowy
from xx_zrodlo where id_portal is null)
open tb
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.nu
mer_pos,@.kierunkowy
while @.@.fetch_status = 0
begin
IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
BEGIN
INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
select @.@.Identity
set @.tb_firma_identity = @.@.Identity
--select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
VALUES (@.tb_firma_identity,@.id_branza,30000,1)
INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
(@.ulica,@.numer_pos,@.kod,@.id_miasto)
select @.@.Identity
set @.tb_adres_identity = @.@.Identity
--select @.id_adres = max(idTB_ADRES) from TB_ADRES
INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
(@.tb_firma_identity,@.tb_adres_identity,1
)
--przypadek telefonu komrkowego
--IF substring(@.telefon,1,1) = 0
IF @.kierunkowy = 0
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,5,@.tb_adres_identity,1)
END
--przypadek telefonu stacjonarnego
--IF substring(@.telefon,1,1) != 0
IF @.kierunkowy <>0 AND @.kierunkowy is not null
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.telefon,2,@.tb_adres_identity,1)
END
--przypadek adresu www
IF len(@.www) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.www,3,@.tb_adres_identity,1)
END
--przypadek adresu email
IF len(@.mail) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@.mail,4,@.tb_adres_identity,1)
END
END
fetch next from tb into
@.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.nu
mer_pos,@.kierunkowy
END
end
close tb
deallocate tb
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi
It seems that the id column is a natural key for this and therefore you
could do this using set based operations and not a cursor. This would speed
up your process.
John
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23Qw81E1zGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi
> I encountered a problem when I'm trying to import data from a flat table
> to several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO
> to several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,
id_branza,www,mail,kod,numer_pos,k
ierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1
)
> --przypadek telefonu komrkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>|||Dariusz Tomon wrote:
> Hi
> I encountered a problem when I'm trying to import data from a flat table t
o
> several tables of the same databases.
> There are about 1 mln records inside the flat table called XX_ZRODLO. The
> stored procedure for import seems to work and import data but after about
> 15000 it stops and it seems as though QA is hung up.
> After restart of SQL and shutdown QA - the procedure for import can import
> another about 30000 records and then the same agian QA is dead ...
> Where could be the problem?
> This is the procedure to import data (1 mln records) from table XX_ZRODLO
to
> several tables of databases according to its structure.
> CREATE procedure XX_IMPORT
> as
> --##### SEKCJA DEKLARACJI
> declare @.nazwa nvarchar(255)
> declare @.id int
> declare @.id_woj int
> declare @.id_miasto int
> declare @.ulica nvarchar(255)
> declare @.telefon nvarchar(255)
> declare @.id_branza int
> declare @.www nvarchar(255)
> declare @.mail nvarchar(255)
> --nowo dodane
> declare @.kod nvarchar(255)
> declare @.numer_pos nvarchar(255)
> declare @.kierunkowy nvarchar(255)
> declare @.tb_firma_identity int
> declare @.tb_adres_identity int
> --koniec
> declare @.id_firma int
> declare @.id_adres int
> set @.nazwa = ''
> set @.id = 0
> set @.id_woj = 0
> set @.id_miasto=0
> set @.ulica=''
> set @.telefon=''
> set @.kierunkowy=''
> set @.id_branza=0
> set @.www=''
> set @.mail=''
> --nowo dodane
> set @.kod = ''
> set @.numer_pos =''
> set @.kierunkowy =''
> --koniec
> -- ####### KONIEC SEKCJI DEKLARACJI
>
> --######## POCZATEK
> begin
>
> declare tb cursor for (select
> nazwa,id,id_woj,id_miasto,ulica,telefon,
id_branza,www,mail,kod,numer_pos,k
ierunkowy
> from xx_zrodlo where id_portal is null)
>
> open tb
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> while @.@.fetch_status = 0
> begin
>
> IF @.id_miasto <>0 AND @.id_miasto is not NULL AND @.id_woj<>0 AND @.id_woj is
> not NULL AND @.id_branza<>0 AND @.id_branza is not NULL
> BEGIN
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@.nazwa,@.id)
> select @.@.Identity
> set @.tb_firma_identity = @.@.Identity
> --select @.id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@.id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@.tb_firma_identity,@.id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@.ulica,@.numer_pos,@.kod,@.id_miasto)
> select @.@.Identity
> set @.tb_adres_identity = @.@.Identity
> --select @.id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@.tb_firma_identity,@.tb_adres_identity,1
)
> --przypadek telefonu komrkowego
> --IF substring(@.telefon,1,1) = 0
> IF @.kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,5,@.tb_adres_identity,1)
> END
> --przypadek telefonu stacjonarnego
> --IF substring(@.telefon,1,1) != 0
> IF @.kierunkowy <>0 AND @.kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.telefon,2,@.tb_adres_identity,1)
> END
> --przypadek adresu www
> IF len(@.www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.www,3,@.tb_adres_identity,1)
> END
> --przypadek adresu email
> IF len(@.mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@.mail,4,@.tb_adres_identity,1)
> END
> END
>
> fetch next from tb into
> @.nazwa,@.id,@.id_woj,@.id_miasto,@.ulica,@.te
lefon,@.id_branza,@.www,@.mail,@.kod,@.
numer_pos,@.kierunkowy
> END
> end
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
Launch another QA session and look at the sysprocesses table - is there
blocking? Does the spid that your import is using show a waittype? Are
you see a delay due to autogrow?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)