> 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

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9833C9091E658Yazorman@127.0.0.1...
> Dariusz Tomon (d.to***@mazars.pl) writes:
>> 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?
>
> The quick fix would be to add SET NOCOUNT ON first in the procedure. I
> would guess that is all those (1 row affected) that makes QA choke.
>
> If you want some progress indication, you can add a
>
>   RAISERROR('Done %d rows', 0, 1, @rows) WITH NOWAIT
>
> that you issue every 1000 rows or so.
>
> A more thorough rework would be to operate set-based and insert all
> rows in one go. (Or in chunks of, say, 100000 rows). Since you use
> IDENTITY column, this will be a bit painful.
>
> If this import is a one-off, rewriting into set-based logic is probably
> not worth it, but this import is going to happen frequently, it's
> imperative that you rewrite it.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

> Dariusz Tomon (d.to***@mazars.pl) writes:
> > What do you think about using DTS for my import is it faster then my
> > procedure? (It is mentioned in the first answer for my post)
>
> I would never use DTS. Then again, that's because I don't know DTS and
> what it's good for.
>
> But as I understand it, DTS is what you use when you have a file and want
> to import it, possibly with some transformation on the way. You already
> have your data in a table, so apply DTS to that is maybe not the way to
> do it. Then again, I guess that the data came from a file originally.
>
> If you have used DTS before and are confident, there may be reason to
> consider it. But if you have never used it before, I don't think it's
> worth the pain. But as I said, I don't know DTS, so I am not really the
> right person to ask.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Stu" <stuart.ainswo***@gmail.com> wrote in message
news:1157336386.544212.211190@e3g2000cwe.googlegroups.com...
> As Erland's post suggests,  giving bad advice about a product is worse
> than no advice, so I aprreciate the fact that he refuses to give you
> inaccurate information about DTS becasue of his lack of experience with
> the product.
>
> DTS is ver ypowerful for moving data baetween data sources (including
> not only flat files but any data source that has an OLEDB or ODBC
> connection avaialble).  One of the benefits of DTS is that it can very
> fast and very effecient at moving and transforming data from one format
> to another; I use the term format to include both file storage (as in
> flat file, SQL table, Excel sheet) AND organization (denormalized to
> normalize and vice versa).
>
> Although DTS is primitive compared to SSIS, it still has a number of
> benefit:
> 1.  Complex transformations can be done on-th-fly using VBScript.
> 2.  Complex error handling and data cleansing manuevers can be be
> performed.
> 3.  Data can be mamipulated using T-SQL, Perl, VBScript, JavaScript.
>
> However, as Erlanda also suggested, if you haven't used DTS before, it
> can be tricky to pick up an learn.  However, there a number of good
> resources available, and to me, it's one of the most benefecial tools
> in the SQL Server suite.
>
> Just my .02,
> Stu
>
> Erland Sommarskog wrote:
>> Dariusz Tomon (d.to***@mazars.pl) writes:
>> > What do you think about using DTS for my import is it faster then my
>> > procedure? (It is mentioned in the first answer for my post)
>>
>> I would never use DTS. Then again, that's because I don't know DTS and
>> what it's good for.
>>
>> But as I understand it, DTS is what you use when you have a file and want
>> to import it, possibly with some transformation on the way. You already
>> have your data in a table, so apply DTS to that is maybe not the way to
>> do it. Then again, I guess that the data came from a file originally.
>>
>> If you have used DTS before and are confident, there may be reason to
>> consider it. But if you have never used it before, I don't think it's
>> worth the pain. But as I said, I don't know DTS, so I am not really the
>> right person to ask.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

> Dariusz Tomon (d.to***@mazars.pl) writes:
> > Thank you very much for your explanation - I will try to use DTS to my
> > import. Do you know any good manual for using DTS in the internet?
>
> It should be documented in Books Online. Which is on you hard disk, but
> see below for updates.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx