Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.sql

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import flat file into SQL Server 2005 Express

I am new to SQL Server, and migrating part of an Access application to
SSE. I am trying to insert a comma delimited file into SSE 2005. I am
able to run a BULK INSERT statement on a simple file, specifying the
field (,) and row (\n) terminators. I can also do the same with a
format file.

Here is the problem. My csv file has 185 columns, with a mixture of
datatypes. Sometimes, a text field will contain the field delimiter as
part of the string. In this case (and only in this case) there will be
double quotes around the string to indicate that the comma is part of
the field, and not a delimiter.

Is there any way to indicate that there is a text delimiter that is
only present some of the time?

If not, any suggestions on getting the data into SSE?

Many thanks for your input.

Cheryl(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

I am new to SQL Server, and migrating part of an Access application to
SSE. I am trying to insert a comma delimited file into SSE 2005. I am
able to run a BULK INSERT statement on a simple file, specifying the
field (,) and row (\n) terminators. I can also do the same with a
format file.
>
Here is the problem. My csv file has 185 columns, with a mixture of
datatypes. Sometimes, a text field will contain the field delimiter as
part of the string. In this case (and only in this case) there will be
double quotes around the string to indicate that the comma is part of
the field, and not a delimiter.

So a file could look like this:

2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie

There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

Most other people would probably try to write a package in Integration
Services, but I have never used Integration Services myself. And for your
part - SQL Express does not come with Integration Services, I believe.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In message <Xns98B096E86FA00Yazorman@.127.0.0.1>, Erland Sommarskog
<esquel@.sommarskog.sewrites

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

>I am new to SQL Server, and migrating part of an Access application to
>SSE. I am trying to insert a comma delimited file into SSE 2005. I am
>able to run a BULK INSERT statement on a simple file, specifying the
>field (,) and row (\n) terminators. I can also do the same with a
>format file.
>>
>Here is the problem. My csv file has 185 columns, with a mixture of
>datatypes. Sometimes, a text field will contain the field delimiter as
>part of the string. In this case (and only in this case) there will be
>double quotes around the string to indicate that the comma is part of
>the field, and not a delimiter.

>
>So a file could look like this:
>
2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie
>
>There is now way to get BULK INSERT to handle this file in that shape.
>If I were faced with this file, I would write Perl script that replaced
>the commas outside the "" with a different delimiter and then removed the
>"". And it would not be trivial.
>
>Most other people would probably try to write a package in Integration
>Services, but I have never used Integration Services myself. And for your
>part - SQL Express does not come with Integration Services, I believe.

Two things to add, both useful options if the amount of data is small.
First, the import filters in MS Access are better than those in SQL
Server. If the data will fit into an Access table that might just do the
trick. Second, spreadsheets have more flexible parsing options than
databases. It may be possible to load the data into a spreadsheet. That
allows different algorithms to be applied to different rows.

Lastly, text files can be opened and read by VBA code in any of the
office languages, or any of the .NET languages. Either could be used,
but writing code to cope with all of the possible options may take time.

--
Bernard Peek
back in search of cognoscenti|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

So a file could look like this:
>
2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie
>
There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

In addition to Bernard's post, is not Excel able to read that format?
In such case open in Except, and save as a tab-delimited file and importing
that should be a breeze. (Assuming, of course, there are no tabs in the
data!)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

So a file could look like this:

2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie

There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

>
In addition to Bernard's post, is not Excel able to read that format?
In such case open in Except, and save as a tab-delimited file and importing
that should be a breeze. (Assuming, of course, there are no tabs in the
data!)
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
at 65536 rows?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

>Thank you both for your suggestions. Yes, I was thinking that BULK
>INSERT was not going to be able to handle this. I had thought about
>dumping the file into an Access table first, but the file could be very
>large (200,000+ rows). I am going to try the Excel spreadsheet idea.

>
200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
at 65536 rows?

I think the latest version of Excel may have a higher row limit - which
only increases the tendency of newbies to misuse Excel as a "database".|||You are correct - Excel has a limit on the number of rows. I thought
about that after I sent the reply. So now I am looking at Access.

Here is my next question. I want to use OPENROWSET in a procedure to
get the data from Access into SSE. My code looks something like this:

INSERT INTO sse_table1 Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'path to mdb';'admin';'',
'Select * FROM access_table1
) as GFF

This works great. However, the location of the access database is only
known at runtime. I can pass the path as a parameter to the stored
procedure, but using it as a variable in OPENROWSET fails. Code looks
like this

CREATE PROCEDURE [dbo].[spImportBillingFile]
@.strTableLocation varchar(255),
@.btSuccess bit OUTPUT
AS
BEGIN

DECLARE @.strConnect varchar(255)
SET @.strConnect = @.strTableLocation

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @.strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF

Set @.btSuccess = 1

END

Does OPENROWSET not allow a variable to be used?

Thanks again for the help.

Ed Murphy wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:
>

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
at 65536 rows?

>
I think the latest version of Excel may have a higher row limit - which
only increases the tendency of newbies to misuse Excel as a "database".

|||(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @.strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF
>
Set @.btSuccess = 1
>
END
>
Does OPENROWSET not allow a variable to be used?

No. Either you have to use dynamic SQL, or define a linked server on the
fly. The former is probably simpler. Look at
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for a similar example
on how to deal with the nested strings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for all the info - I figured out how to use the dynamic sql.

Erland Sommarskog wrote:

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @.strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF

Set @.btSuccess = 1

END

Does OPENROWSET not allow a variable to be used?

>
No. Either you have to use dynamic SQL, or define a linked server on the
fly. The former is probably simpler. Look at
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for a similar example
on how to deal with the nested strings.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

sql

Import flat file data

Hi,

I wanted to know if there was a way to import data from a flat file without specifiying the delimiters. I want to import each line in one row so that i can use the substring function to break of the data as an when i want and not as per the delimited format file or the wizard.

i.e if row one had "abc"|"1453"|"Jack"|"Smith"| etc.... rather than importing these as different columns and rows. I want this all in one row, one column.

Is it Possible?

Hi,

You should use the bcp utility with the default -t switch. The default field terminator is the tab. I did not try this, but it should work.

Regards,

Janos

|||

You want to concatenate the data then - just use a "fixed width" as long as the string you want to make. You'll get the quotes as well.

To remove the quotes and add a new delimter at the same time, you could use the "sed" program or something like it to form the text file the way you want it.

|||

what's the "sed" program? and also if i use the wizard and it didn't have a CR or LF then it would throw an error which why I wanted to know if i could import the data via a script? if so how?

|||

"what's the "sed" program? and also if i use the wizard and it didn't have a CR or LF then it would throw an error which why I wanted to know if i could import the data via a script? if so how?"

The sed program is an open-source string processor. There's stuff about it all over the web. I didn't realize that you were using a wizard. There are other methods for import, and you might start here for more ways to do that:

http://search.live.com/results.aspx?q=import+data+%22SQL+Server%22&src=IE-SearchBox

|||

Use BULK INSERT statement to insert each line as one column for one row on temp table.

And then split each row data by the pipe delimiter for separate column.

Doing that you can verify the column data before insert into data table

import flat file

I am trying to import a flat file using SQL Server Management Studio and am receiveing the error:

Error 0xc0202055: Data Flow Task: The column delimiter for column "Column 19" was not found.
(SQL Server Import and Export Wizard)

I would like to capture the rows that are causing the error and have the import continue. Am I able to edit the behavior somehow?

Thanks.

If I recall correctly; in one of the last steps of the import wizard you are asked to either execute the package immediately or to save it as a .dtsx file.

Chose to save it as a .dtsx files and the use BIDS to edit it according to your needs.

|||

Hi Rafael, thanks for responding. I get to the screen where it tells me that ' The package will be saved to the instance of the SQL Server Database Engine ...', but it doesn't allow me to choose the file extension. Yes, I'm very new at this.

Thank you again, I really appreciate your help.

|||

Hi, I was able to save the .dtsx file. Now on to BIDS.

Thanks.

|||

I've been trying to edit the package in BIDS but am not having success. I'm importing a flat file and am trying to write the records that do not get imported successfully to an error file. If anyone can help me out.....

Thanks.

|||

tantz wrote:

I've been trying to edit the package in BIDS but am not having success. I'm importing a flat file and am trying to write the records that do not get imported successfully to an error file. If anyone can help me out.....

Thanks.

You will have to tell us what you are not having success with first.|||

Well, I read that you can 'use the Error Output Page of the Flat File Source Editor dialog box to select error-handling options'. How do I do this? Thanks.

|||

tantz wrote:

Well, I read that you can 'use the Error Output Page of the Flat File Source Editor dialog box to select error-handling options'. How do I do this? Thanks.

On the data flow, you grab the red arrow of the flat file source and connect it to a destination (flat file, sql server, etc...)|||

Thanks Phil. I was able to connect it to a flat file, and am getting the same errors. Where can we find this file with the errors? Also, is there a way to make the package continue processing even when there are errors?

I really appreciate your time and help.

|||

Open the Flat file Source editor; go to the error output page and make sure all the values in Error and truncation columns are 'Redirect Row'. Tip You can Shift+ Select al of them and then use the 'Set this value to the selected Cells'. After doing that, make sure you connect the red arrow to a file or any other destination; so you can examine it later.

|||Hi, thanks for your reply.
I don't think I'm looking in the right place - can you tell me exactly how you're getting to the error output page. Thanks so much.|||When you double click on the flat file source, there is a button, "error output" or something like that.|||

Cool, I got it, but I still am not able to see the records. I've connected the flat file source with a red arrow 'flat file source error output'. What else am I missing? Anything with mappings? An error list is displayed at the bottom, but it has 0 errors, 0 warnings, 0 messages, but I know I am getting errors.

Thanks so much.

|||

Hi, I think I've worked out the error output, but I seem to have created a different problem. I am now getting a message 'cannot open the data file. the process cannot access the file because it is being used by another process'.

It's a .txt file. Nothing else is using it. I just ran something in SQL Server Management Studio using the same file and it works fine. Is it possible that I changed something in the .dtsx package when I was trying to get the error output to work?

If anyone can help me out -
Thanks.

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

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