Import only yhe changed data

I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table.
THX
InonIt would be bit difficult to import without a time field, which is key to compile the data.

If your task is about changed data then why not consider replication.|||Sure, but you need a staging environment...

1. bcp (I can't abvide DTS unless the data is in Excel or Access, even then...) in to a stage table
2. Write 3 sql statements to compare new data with old...determine, based on keys, which data was added, which data was deleted, and which data was updated..

3. Then INSERT, DELETE and UPDATE those sets of data...

Got a link somewhere...

Hold on...|||Here's the code:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28305|||You can use the BINARY_CHECKSUM function to determine whether data was changed.sql

import of data in text file How ?

Hello group,
Each month we want to import data from an ascii file,
the records are of a fixed length (some spare space is added to fill them
up)
there are 12 different types of records (variants)
Each type has a different (but within the type always the same) build.
Each record starts with a type indication. Then the records follows.
One type can have 2 fields the other type can have 15 fields.
Records following eachother can have relations, so a type two record
belongs to the type 1 record before the type 2 record.
Example with 3 types
1 AAAAAABB
2 CCCDDDDDDEEEEFFGGHH
2 CCCDDDDDDEEEEFFGGHH
3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
1 AAAAAABB
2 CCCDDDDDDEEEEFFGGHH
The letters are indications for the different fields.
Any suggestions how to solve this ?
In our propriety system this was solved by reading the record
into a pascal record with all twelf variants and then writing it to the
propriety database. We could reuse the technique of the program,
but can not reuse the program.
And we still have to split up each record into fields.
Our propriety database just accepted the 'binairy' data as is.
ben brugmanBen,
You can reimplement your method of reading a record at a time and inserting
the row into the proper destination by using a DTS package and doing some
scripting for each row.
How I tend to do things like this is:
BULK INSERT the file to a work table
CREATE TABLE BulkInsertWork
(RowID INT IDENTITY,
RowText NVARCHAR(1000))
TRUNCATE TABLE BulkInsertWork
BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
Then I write a stored procedure that parses out the specific rows, such as:
INSERT INTO Table1 (ColumnA, ColumnB)
SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
FROM BulkInsertWork
etc. for each type
FWIW
Russell Fields
http:/www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-even dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:bob7k7$4dj$1@.reader11.wxs.nl...
> Hello group,
> Each month we want to import data from an ascii file,
> the records are of a fixed length (some spare space is added to fill them
> up)
> there are 12 different types of records (variants)
> Each type has a different (but within the type always the same) build.
> Each record starts with a type indication. Then the records follows.
> One type can have 2 fields the other type can have 15 fields.
> Records following eachother can have relations, so a type two record
> belongs to the type 1 record before the type 2 record.
> Example with 3 types
> 1 AAAAAABB
> 2 CCCDDDDDDEEEEFFGGHH
> 2 CCCDDDDDDEEEEFFGGHH
> 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> 1 AAAAAABB
> 2 CCCDDDDDDEEEEFFGGHH
> The letters are indications for the different fields.
> Any suggestions how to solve this ?
> In our propriety system this was solved by reading the record
> into a pascal record with all twelf variants and then writing it to the
> propriety database. We could reuse the technique of the program,
> but can not reuse the program.
> And we still have to split up each record into fields.
> Our propriety database just accepted the 'binairy' data as is.
> ben brugman
>|||I immeadiatly went ahead with your suggested method.
I created a .txt file from the example in the mail.
I changed the nvarchar in the table to varchar.
When inserting from the .txt file I get the following errors :
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
The statement has been terminated.
Added :
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n'
)
But stil the same errors.
(Lastrow and rowsperbatch did not bring a solution either).
Please advise.
Also played around a bit with DTS, but the problem is that I can do an
import and then
a selection. Or that I can do a split up of the line into different fields.
But I can not do them both.
(Or I have to split up every line in every possible way and after selection
throwing away
the not desired results. Make a table for each type and insert every row in
every table and
then trowing away the types which do not belong in the table. I think this
is pretty ugly).
Ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Ben,
> You can reimplement your method of reading a record at a time and
inserting
> the row into the proper destination by using a DTS package and doing some
> scripting for each row.
> How I tend to do things like this is:
> BULK INSERT the file to a work table
> CREATE TABLE BulkInsertWork
> (RowID INT IDENTITY,
> RowText NVARCHAR(1000))
> TRUNCATE TABLE BulkInsertWork
> BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> Then I write a stored procedure that parses out the specific rows, such
as:
> INSERT INTO Table1 (ColumnA, ColumnB)
> SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> FROM BulkInsertWork
> etc. for each type
> FWIW
> Russell Fields
> http:/www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-even dedicated to SQL Server! Register TODAY!
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bob7k7$4dj$1@.reader11.wxs.nl...
> > Hello group,
> >
> > Each month we want to import data from an ascii file,
> > the records are of a fixed length (some spare space is added to fill
them
> > up)
> > there are 12 different types of records (variants)
> > Each type has a different (but within the type always the same) build.
> > Each record starts with a type indication. Then the records follows.
> > One type can have 2 fields the other type can have 15 fields.
> >
> > Records following eachother can have relations, so a type two record
> > belongs to the type 1 record before the type 2 record.
> >
> > Example with 3 types
> >
> > 1 AAAAAABB
> > 2 CCCDDDDDDEEEEFFGGHH
> > 2 CCCDDDDDDEEEEFFGGHH
> > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > 1 AAAAAABB
> > 2 CCCDDDDDDEEEEFFGGHH
> >
> > The letters are indications for the different fields.
> >
> > Any suggestions how to solve this ?
> >
> > In our propriety system this was solved by reading the record
> > into a pascal record with all twelf variants and then writing it to the
> > propriety database. We could reuse the technique of the program,
> > but can not reuse the program.
> > And we still have to split up each record into fields.
> > Our propriety database just accepted the 'binairy' data as is.
> >
> > ben brugman
> >
> >
>|||Ben,
A guess: The last row of the text file does not have the '\n'. If that is
so, append the character.
Less likely and more obscure: Are you on SQL Server 2000? If you are on
7.0, KB article 324122 has obscure problems that it is unlikely apply,
but...
http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
All error numbers that are in the range of 7300 to 7399 indicate a problem
with the provider, in this case STREAM.
Russell Fields
http://www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-event dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> I immeadiatly went ahead with your suggested method.
> I created a .txt file from the example in the mail.
> I changed the nvarchar in the table to varchar.
> When inserting from the .txt file I get the following errors :
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> The statement has been terminated.
>
> Added :
> WITH (
> DATAFILETYPE = 'char',
> ROWTERMINATOR = '\n'
> )
> But stil the same errors.
> (Lastrow and rowsperbatch did not bring a solution either).
> Please advise.
> Also played around a bit with DTS, but the problem is that I can do an
> import and then
> a selection. Or that I can do a split up of the line into different
fields.
> But I can not do them both.
> (Or I have to split up every line in every possible way and after
selection
> throwing away
> the not desired results. Make a table for each type and insert every row
in
> every table and
> then trowing away the types which do not belong in the table. I think this
> is pretty ugly).
> Ben brugman
>
>
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Ben,
> >
> > You can reimplement your method of reading a record at a time and
> inserting
> > the row into the proper destination by using a DTS package and doing
some
> > scripting for each row.
> >
> > How I tend to do things like this is:
> > BULK INSERT the file to a work table
> >
> > CREATE TABLE BulkInsertWork
> > (RowID INT IDENTITY,
> > RowText NVARCHAR(1000))
> >
> > TRUNCATE TABLE BulkInsertWork
> >
> > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> >
> > Then I write a stored procedure that parses out the specific rows, such
> as:
> >
> > INSERT INTO Table1 (ColumnA, ColumnB)
> > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > FROM BulkInsertWork
> >
> > etc. for each type
> >
> > FWIW
> > Russell Fields
> > http:/www.sqlpass.org/events/seattle03
> > 2003 PASS Community Summit - Seattle
> > - The largest user-even dedicated to SQL Server! Register TODAY!
> >
> >
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > Hello group,
> > >
> > > Each month we want to import data from an ascii file,
> > > the records are of a fixed length (some spare space is added to fill
> them
> > > up)
> > > there are 12 different types of records (variants)
> > > Each type has a different (but within the type always the same) build.
> > > Each record starts with a type indication. Then the records follows.
> > > One type can have 2 fields the other type can have 15 fields.
> > >
> > > Records following eachother can have relations, so a type two record
> > > belongs to the type 1 record before the type 2 record.
> > >
> > > Example with 3 types
> > >
> > > 1 AAAAAABB
> > > 2 CCCDDDDDDEEEEFFGGHH
> > > 2 CCCDDDDDDEEEEFFGGHH
> > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > 1 AAAAAABB
> > > 2 CCCDDDDDDEEEEFFGGHH
> > >
> > > The letters are indications for the different fields.
> > >
> > > Any suggestions how to solve this ?
> > >
> > > In our propriety system this was solved by reading the record
> > > into a pascal record with all twelf variants and then writing it to
the
> > > propriety database. We could reuse the technique of the program,
> > > but can not reuse the program.
> > > And we still have to split up each record into fields.
> > > Our propriety database just accepted the 'binairy' data as is.
> > >
> > > ben brugman
> > >
> > >
> >
> >
>|||After loads of trying, reading the KB file, still getting the same Error
message.
In the end we deleted the identity column from "BulkInsertWork" and now the
insert works, but offcourse we have lost the order of the table.
Also did try some DTS work, but DTS is not very flexible when it comes to
variant records in a text file.
Thanks for your time,
and if you have suggestions to bring the identity column back,
I would appreciate that.
ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Ben,
> A guess: The last row of the text file does not have the '\n'. If that
is
> so, append the character.
> Less likely and more obscure: Are you on SQL Server 2000? If you are on
> 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> but...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> All error numbers that are in the range of 7300 to 7399 indicate a problem
> with the provider, in this case STREAM.
> Russell Fields
> http://www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-event dedicated to SQL Server! Register TODAY!
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > I immeadiatly went ahead with your suggested method.
> > I created a .txt file from the example in the mail.
> > I changed the nvarchar in the table to varchar.
> >
> > When inserting from the .txt file I get the following errors :
> >
> > Server: Msg 4832, Level 16, State 1, Line 1
> > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'STREAM' reported an error. The provider did not give
any
> > information about the error.
> > The statement has been terminated.
> >
> >
> > Added :
> > WITH (
> > DATAFILETYPE = 'char',
> > ROWTERMINATOR = '\n'
> > )
> >
> > But stil the same errors.
> > (Lastrow and rowsperbatch did not bring a solution either).
> >
> > Please advise.
> >
> > Also played around a bit with DTS, but the problem is that I can do an
> > import and then
> > a selection. Or that I can do a split up of the line into different
> fields.
> > But I can not do them both.
> > (Or I have to split up every line in every possible way and after
> selection
> > throwing away
> > the not desired results. Make a table for each type and insert every row
> in
> > every table and
> > then trowing away the types which do not belong in the table. I think
this
> > is pretty ugly).
> >
> > Ben brugman
> >
> >
> >
> >
> >
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > Ben,
> > >
> > > You can reimplement your method of reading a record at a time and
> > inserting
> > > the row into the proper destination by using a DTS package and doing
> some
> > > scripting for each row.
> > >
> > > How I tend to do things like this is:
> > > BULK INSERT the file to a work table
> > >
> > > CREATE TABLE BulkInsertWork
> > > (RowID INT IDENTITY,
> > > RowText NVARCHAR(1000))
> > >
> > > TRUNCATE TABLE BulkInsertWork
> > >
> > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> > >
> > > Then I write a stored procedure that parses out the specific rows,
such
> > as:
> > >
> > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > FROM BulkInsertWork
> > >
> > > etc. for each type
> > >
> > > FWIW
> > > Russell Fields
> > > http:/www.sqlpass.org/events/seattle03
> > > 2003 PASS Community Summit - Seattle
> > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > >
> > >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > Hello group,
> > > >
> > > > Each month we want to import data from an ascii file,
> > > > the records are of a fixed length (some spare space is added to fill
> > them
> > > > up)
> > > > there are 12 different types of records (variants)
> > > > Each type has a different (but within the type always the same)
build.
> > > > Each record starts with a type indication. Then the records follows.
> > > > One type can have 2 fields the other type can have 15 fields.
> > > >
> > > > Records following eachother can have relations, so a type two record
> > > > belongs to the type 1 record before the type 2 record.
> > > >
> > > > Example with 3 types
> > > >
> > > > 1 AAAAAABB
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > 1 AAAAAABB
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > >
> > > > The letters are indications for the different fields.
> > > >
> > > > Any suggestions how to solve this ?
> > > >
> > > > In our propriety system this was solved by reading the record
> > > > into a pascal record with all twelf variants and then writing it to
> the
> > > > propriety database. We could reuse the technique of the program,
> > > > but can not reuse the program.
> > > > And we still have to split up each record into fields.
> > > > Our propriety database just accepted the 'binairy' data as is.
> > > >
> > > > ben brugman
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ben,
Yes, I am sorry that I glossed this in my earlier quick explanation. (I
thought about it during the night, don't you know.)
You need a format file to tell bulk insert to skip over the Identity column.
Something like:
8.0
2
1 SQLCHAR 0 0 "" 1 ID SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 1000 "\r\n" 2 Text SQL_Latin1_General_Cp437_BIN
Russell Fields
http://www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-event dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:OiAr$FTpDHA.3844@.tk2msftngp13.phx.gbl...
> After loads of trying, reading the KB file, still getting the same Error
> message.
> In the end we deleted the identity column from "BulkInsertWork" and now
the
> insert works, but offcourse we have lost the order of the table.
> Also did try some DTS work, but DTS is not very flexible when it comes to
> variant records in a text file.
> Thanks for your time,
> and if you have suggestions to bring the identity column back,
> I would appreciate that.
> ben brugman
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Ben,
> >
> > A guess: The last row of the text file does not have the '\n'. If that
> is
> > so, append the character.
> >
> > Less likely and more obscure: Are you on SQL Server 2000? If you are
on
> > 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> > but...
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> >
> > All error numbers that are in the range of 7300 to 7399 indicate a
problem
> > with the provider, in this case STREAM.
> >
> > Russell Fields
> > http://www.sqlpass.org/events/seattle03
> > 2003 PASS Community Summit - Seattle
> > - The largest user-event dedicated to SQL Server! Register TODAY!
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > > I immeadiatly went ahead with your suggested method.
> > > I created a .txt file from the example in the mail.
> > > I changed the nvarchar in the table to varchar.
> > >
> > > When inserting from the .txt file I get the following errors :
> > >
> > > Server: Msg 4832, Level 16, State 1, Line 1
> > > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > > Server: Msg 7399, Level 16, State 1, Line 1
> > > OLE DB provider 'STREAM' reported an error. The provider did not give
> any
> > > information about the error.
> > > The statement has been terminated.
> > >
> > >
> > > Added :
> > > WITH (
> > > DATAFILETYPE = 'char',
> > > ROWTERMINATOR = '\n'
> > > )
> > >
> > > But stil the same errors.
> > > (Lastrow and rowsperbatch did not bring a solution either).
> > >
> > > Please advise.
> > >
> > > Also played around a bit with DTS, but the problem is that I can do an
> > > import and then
> > > a selection. Or that I can do a split up of the line into different
> > fields.
> > > But I can not do them both.
> > > (Or I have to split up every line in every possible way and after
> > selection
> > > throwing away
> > > the not desired results. Make a table for each type and insert every
row
> > in
> > > every table and
> > > then trowing away the types which do not belong in the table. I think
> this
> > > is pretty ugly).
> > >
> > > Ben brugman
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > Ben,
> > > >
> > > > You can reimplement your method of reading a record at a time and
> > > inserting
> > > > the row into the proper destination by using a DTS package and doing
> > some
> > > > scripting for each row.
> > > >
> > > > How I tend to do things like this is:
> > > > BULK INSERT the file to a work table
> > > >
> > > > CREATE TABLE BulkInsertWork
> > > > (RowID INT IDENTITY,
> > > > RowText NVARCHAR(1000))
> > > >
> > > > TRUNCATE TABLE BulkInsertWork
> > > >
> > > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> > > >
> > > > Then I write a stored procedure that parses out the specific rows,
> such
> > > as:
> > > >
> > > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > > FROM BulkInsertWork
> > > >
> > > > etc. for each type
> > > >
> > > > FWIW
> > > > Russell Fields
> > > > http:/www.sqlpass.org/events/seattle03
> > > > 2003 PASS Community Summit - Seattle
> > > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > > >
> > > >
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > > Hello group,
> > > > >
> > > > > Each month we want to import data from an ascii file,
> > > > > the records are of a fixed length (some spare space is added to
fill
> > > them
> > > > > up)
> > > > > there are 12 different types of records (variants)
> > > > > Each type has a different (but within the type always the same)
> build.
> > > > > Each record starts with a type indication. Then the records
follows.
> > > > > One type can have 2 fields the other type can have 15 fields.
> > > > >
> > > > > Records following eachother can have relations, so a type two
record
> > > > > belongs to the type 1 record before the type 2 record.
> > > > >
> > > > > Example with 3 types
> > > > >
> > > > > 1 AAAAAABB
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > > 1 AAAAAABB
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > >
> > > > > The letters are indications for the different fields.
> > > > >
> > > > > Any suggestions how to solve this ?
> > > > >
> > > > > In our propriety system this was solved by reading the record
> > > > > into a pascal record with all twelf variants and then writing it
to
> > the
> > > > > propriety database. We could reuse the technique of the program,
> > > > > but can not reuse the program.
> > > > > And we still have to split up each record into fields.
> > > > > Our propriety database just accepted the 'binairy' data as is.
> > > > >
> > > > > ben brugman
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I'll try that on monday.
At the moment I have done away with the identity column,
I am not sure if I will need it, because it seems that the only dependency
in order of the records is the dependency on the very first record, which
only exists once.
I am using your method of
"SUBSTRING(RowText,3,6), SUBSTRING(RowText,9,2)"
But because there are 153 columns in total, I do not want to type this out.
So I am in the process of writing scripts to generate this code.
Up to know I have managed to get three tables done, but the script
still needs some 'hand editing' for each table.
Using constructs like
Select 'INSERT INTO' + @.@.tablename+ '('
Select 'SUBSTRING(Rowtext,
'+convert(varchar(3),offset)+','+convert(varchar(3),length)+'),'
From metatable where table_name = @.@.tablename and length <> 0
Select 'From Bulkinsertwork'
Or something similar, then I cut and past this into another QA window,
clean it up and execute it.
Some things still to be solved are
The extra comma behind the last SUBSTRING line.
The extra lines generated (with the number of rows affectted and headers).
So I might go and do this in a stringvariabele and try to get the last comma
out.
Haven't decided jet if I am going to deliver the end script or enough tools
to build
the script.
Still some problems with referential constraints and spaces which have to be
converted to
<NULLS>. (After or during ? inserting ?).
Chopping all problems up and going from intermediate result to intermadiate
result makes
the code less complex.
And some problems with the content of the columns coming from other lines.
(The first line contains a sort of number which should be included in almost
all records).
Probably use temporary tables resolve all problems and then copy the content
to the
production tables.
Thanks for you help.
ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ucvlkTTpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Ben,
> Yes, I am sorry that I glossed this in my earlier quick explanation. (I
> thought about it during the night, don't you know.)
> You need a format file to tell bulk insert to skip over the Identity
column.
> Something like:
> 8.0
> 2
> 1 SQLCHAR 0 0 "" 1 ID SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 0 1000 "\r\n" 2 Text SQL_Latin1_General_Cp437_BIN
>
> Russell Fields
> http://www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-event dedicated to SQL Server! Register TODAY!
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:OiAr$FTpDHA.3844@.tk2msftngp13.phx.gbl...
> > After loads of trying, reading the KB file, still getting the same Error
> > message.
> >
> > In the end we deleted the identity column from "BulkInsertWork" and now
> the
> > insert works, but offcourse we have lost the order of the table.
> >
> > Also did try some DTS work, but DTS is not very flexible when it comes
to
> > variant records in a text file.
> >
> > Thanks for your time,
> > and if you have suggestions to bring the identity column back,
> > I would appreciate that.
> >
> > ben brugman
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > > Ben,
> > >
> > > A guess: The last row of the text file does not have the '\n'. If
that
> > is
> > > so, append the character.
> > >
> > > Less likely and more obscure: Are you on SQL Server 2000? If you are
> on
> > > 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> > > but...
> > >
http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> > >
> > > All error numbers that are in the range of 7300 to 7399 indicate a
> problem
> > > with the provider, in this case STREAM.
> > >
> > > Russell Fields
> > > http://www.sqlpass.org/events/seattle03
> > > 2003 PASS Community Summit - Seattle
> > > - The largest user-event dedicated to SQL Server! Register TODAY!
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > > > I immeadiatly went ahead with your suggested method.
> > > > I created a .txt file from the example in the mail.
> > > > I changed the nvarchar in the table to varchar.
> > > >
> > > > When inserting from the .txt file I get the following errors :
> > > >
> > > > Server: Msg 4832, Level 16, State 1, Line 1
> > > > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > > > Server: Msg 7399, Level 16, State 1, Line 1
> > > > OLE DB provider 'STREAM' reported an error. The provider did not
give
> > any
> > > > information about the error.
> > > > The statement has been terminated.
> > > >
> > > >
> > > > Added :
> > > > WITH (
> > > > DATAFILETYPE = 'char',
> > > > ROWTERMINATOR = '\n'
> > > > )
> > > >
> > > > But stil the same errors.
> > > > (Lastrow and rowsperbatch did not bring a solution either).
> > > >
> > > > Please advise.
> > > >
> > > > Also played around a bit with DTS, but the problem is that I can do
an
> > > > import and then
> > > > a selection. Or that I can do a split up of the line into different
> > > fields.
> > > > But I can not do them both.
> > > > (Or I have to split up every line in every possible way and after
> > > selection
> > > > throwing away
> > > > the not desired results. Make a table for each type and insert every
> row
> > > in
> > > > every table and
> > > > then trowing away the types which do not belong in the table. I
think
> > this
> > > > is pretty ugly).
> > > >
> > > > Ben brugman
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > > Ben,
> > > > >
> > > > > You can reimplement your method of reading a record at a time and
> > > > inserting
> > > > > the row into the proper destination by using a DTS package and
doing
> > > some
> > > > > scripting for each row.
> > > > >
> > > > > How I tend to do things like this is:
> > > > > BULK INSERT the file to a work table
> > > > >
> > > > > CREATE TABLE BulkInsertWork
> > > > > (RowID INT IDENTITY,
> > > > > RowText NVARCHAR(1000))
> > > > >
> > > > > TRUNCATE TABLE BulkInsertWork
> > > > >
> > > > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt'
...
> > > > >
> > > > > Then I write a stored procedure that parses out the specific rows,
> > such
> > > > as:
> > > > >
> > > > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > > > FROM BulkInsertWork
> > > > >
> > > > > etc. for each type
> > > > >
> > > > > FWIW
> > > > > Russell Fields
> > > > > http:/www.sqlpass.org/events/seattle03
> > > > > 2003 PASS Community Summit - Seattle
> > > > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > > > >
> > > > >
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > > > Hello group,
> > > > > >
> > > > > > Each month we want to import data from an ascii file,
> > > > > > the records are of a fixed length (some spare space is added to
> fill
> > > > them
> > > > > > up)
> > > > > > there are 12 different types of records (variants)
> > > > > > Each type has a different (but within the type always the same)
> > build.
> > > > > > Each record starts with a type indication. Then the records
> follows.
> > > > > > One type can have 2 fields the other type can have 15 fields.
> > > > > >
> > > > > > Records following eachother can have relations, so a type two
> record
> > > > > > belongs to the type 1 record before the type 2 record.
> > > > > >
> > > > > > Example with 3 types
> > > > > >
> > > > > > 1 AAAAAABB
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > > > 1 AAAAAABB
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > >
> > > > > > The letters are indications for the different fields.
> > > > > >
> > > > > > Any suggestions how to solve this ?
> > > > > >
> > > > > > In our propriety system this was solved by reading the record
> > > > > > into a pascal record with all twelf variants and then writing it
> to
> > > the
> > > > > > propriety database. We could reuse the technique of the program,
> > > > > > but can not reuse the program.
> > > > > > And we still have to split up each record into fields.
> > > > > > Our propriety database just accepted the 'binairy' data as is.
> > > > > >
> > > > > > ben brugman
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

import from excel to SQL Server - can it be more efficient ?

Hello All,

Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ? Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it? Could anyone share with me a snippet of code that does this ?

Here is my code:

Private Sub ProcessRecords()
Dim ds2 As New DataSet
' readExcelSheet is a user-defined function that reads a spreadsheet and returns a DataSet object
ds2 = readExcelSheet("C:\Inetpub\wwwroot\Project1\Book2.xls", "SELECT * FROM [Sheet1$]")
Dim myConnection As SqlConnection = Connection() ' user-defined function that returns a SQLConnection object
myConnection.Open()
Dim strSQL As String = "insert_member" ' stored procedure that inserts records
Dim myCommand As New SqlCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@.salutation", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.firstname", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.lastname", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.company", SqlDbType.NVarChar)

Dim i, j As Integer
Response.Write(Date.Now() & "<br>")
For i = 0 To ds2.Tables("Members").Rows.Count() - 1
myCommand.Parameters("@.salutation").Value = ds2.Tables("Members").Rows(i).Item("sal")
myCommand.Parameters("@.firstname").Value = ds2.Tables("Members").Rows(i).Item("firstname")
myCommand.Parameters("@.lastname").Value = ds2.Tables("Members").Rows(i).Item("lastname")
myCommand.Parameters("@.company").Value = ds2.Tables("Members").Rows(i).Item("company")
j = myCommand.ExecuteNonQuery()
If (j > 0) Then
Response.Write("Record Inserted - " & i + 1 & "<br>")
End If
Next
Response.Write(Date.Now() & "<br>")
myConnection.Close()
End Sub

Please reply soon.
Thank You.can you show us the readExcelObject class?

i got this unspecified exception when trying to connect to an excel file:

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnectionInternal.InitializeProvider() at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnection conn, OleDbConnectionString constr) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnectionBase owningConnection) at System.Data.ProviderBase.DbConnectionClosed.Open(DbConnectionBase outerConnection) at System.Data.ProviderBase.DbConnectionBase.Open() at ASP.excel1_aspx.LoadExcelIntoDS() in c:\excel_dev\excel1.aspx:line 25

<%@. Page language="c#" %>
<%@. Import Namespace="System.Runtime"%>
<%@. Import Namespace="System"%>
<%@. Import Namespace="System.Data"%>
<%@. Import Namespace="System.Data.OleDb"%
<script runat=server>
void Page_Load(Object src, EventArgs e)
{
LoadExcelIntoDS();
}
void LoadExcelIntoDS()
{
OleDbConnection MyConnection = new OleDbConnection();
try
{
String conn = "Provider=Microsoft.Jet.OLEDB.4.0;";
conn += @."Data Source=C:\excel_dev\demo_banc.xls;";
conn += "Extended Properties=Excel 8.0;";
MyConnection = new OleDbConnection(conn);
MyConnection.Open();
}
catch(ArgumentException ae)
{
Response.Write(ae.ToString() + "<BR>");
}
catch(OleDbException e)
{
String err_msg = "";
for (int i = 0; i < e.Errors.Count; i++)
err_msg += "------<br>Index #" + i + "<br>Message: " + e.Errors[i].Message + "<br>NativeError: " + e.Errors[i].NativeError + "<br>Source: " + e.Errors[i].Source + "<br>SQLState: " + e.Errors[i].SQLState + "<br>";
Response.Write(err_msg + "<br>");
Response.Write(e.ToString() + "<BR>");
}
catch(Exception e)
{
Response.Write(e.ToString() + "<br>");
}
finally
{
MyConnection.Close();
}
}|||solved.

pls refer to:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=314899

Import dedicated Records from a Flatfile to a DB

Hello all

I got a Problem when I try to store Data from a Flatfile to a DB.

The following Error appears in the Progress Control:

An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".

I have a Flat File Source, and would like to store the needed records in a DB.

In Column 0 in the Flatfile I have multiple Entries with equal Values.

In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to matchwith the Flatfile?

Thanks in advance for any answer

Chaepp

Chaepp wrote:

Hello all

I got a Problem when I try to store Data from a Flatfile to a DB.

The following Error appears in the Progress Control:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".

I have a Flat File Source, and would like to store the needed records in a DB.

In Column 0 in the Flatfile I have multiple Entries with equal Values.

In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to match with the Flatfile?

Thanks in advance for any answer

Chaepp

your problem seems to cry out for a lookup transformation. you would need to lookup the primary key in the source to see if it exists. if the key already exists, then redirect the row to the error output where you can stage the data for further processing.|||

Hello,

Thanks for your quick answer.

The DB Table is empty and I like to load the data from the Flatfile....

Then I get the error. I tried to go with Lookup, but I have still the same problem...

Could you give me an idea how to solve the problem?

Thanks for an early answer.

Regards

Chaepp

|||

Chaepp wrote:

Hello,

Thanks for your quick answer.

The DB Table is empty and I like to load the data from the Flatfile....

Then I get the error. I tried to go with Lookup, but I have still the same problem...

Could you give me an idea how to solve the problem?

Thanks for an early answer.

Regards

Chaepp

ok, i'll take another stab at it. you could try to use the ole db transformation to insert the rows, and redirect the error rows.|||

Sorry, I'm a beginner.

What do you mean with 'ole db transformation'? The Ole DB Command?

Regards

Chaepp

|||Hi ,
I like to see how this one pans out, as i cannot see how a Lookup would work. By definition Chaepp is importing to a table where the data does not already exist thus the lookup would fail. He could redirect this 'failure' as his error output, but then all his errors would be cached and thus still fail for the same reason as he would have multiple failures on the same data and thus when the data flow passes into the destination to actually write to his database table, it will still fall over. I could see a Lookup working if it would write each row it did not find to the destination table and thus subsequent finds , ie PK violations, could be re-directed, but only if the table is being updated in real time from the lookup which it does not do. I'm thinking a Script component here, not lookup.

Dave|||I tested this in a script component and got it to work. The input iwas a simple flat file of one column that had a couple of duplicates. Destination was a one colum table with a PK on it. The arraylist checks if the current row exists in the array already, if it does it gets ignored, otherwise we add a row to the output buffer which is then sent down the line to the destination task. This works but I could not get it to work from scratch - I had to cut and paste a script component I'd used before into the package because i could not get a new one to generate the Public Class Output0Buffer.... statements. (These are in the file with ' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! at the top so they are system generated as part of the Script Task, I just need to figure out how i got them generated the first time and then we can get the code below to work from a new Script component)

Dim alist As New ArrayList

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not alist.Contains(Row.Name) Then
alist.Add(Row.Name)
Output0Buffer.AddRow()
Output0Buffer.NameOut = Row.Name
End If
End Sub

the Name property of Row comes from the flat file as the Input column
NameOut of Outpu0Buffer is the Script Output column|||

Hi

Thank you very much for your help.

I have found another solution:

I simply use the Aggregate function and it works perfect....

Regards

Chaepp

|||

Chaepp wrote:

Hi

Thank you very much for your help.

I have found another solution:

I simply use the Aggregate function and it works perfect....

Regards

Chaepp

can you please be a little more specific? i don't see how the aggregate transformation would solve your problem.

btw, by "ole db" i meant "ole db command". sorry about the confusion.

Import DBF File into SQL Server using vb.net

Hi,
I need to import dbf file into sql server. This dbf file is about 300 MB
with a 250,000 records. The import process needs to happen once a month.
I can create a DTS package and schedule it but the problem is that the
structure of DBF file (i.e. no. of columns) changes.
So, my solution was to:
1.) Read the schema of dbf file.
2.) Truncate and drop the table if it exist.
3.) Create table accordingly to the schema read in step 1.
4.) Output the dbf file to an xml file. (This step fails).
5.) Read the xml file. (never reaches this step).
6.) Create an index for a column that is going to be used for searching
records. (Hopefully will reach to this step).
Following is my code for step 4:
Dim strSql, strMsg As String
Dim myConnection As New OleDbConnection(m_strDBFConnection)
myConnection.Open()
Dim myCommand As New OleDbCommand(strSql, myConnection)
myCommand.CommandText = "Select Parcel.* From Parcel"
Dim myAdapter As New OleDbDataAdapter
myAdapter.SelectCommand = myCommand
Dim myDS As New DataSet
myAdapter.Fill(myDS) 'Take a long time here. In sql server it
take about 2 minutes to read th parcel table.
myDS.WriteXml("C:\parcel.xml")
I am using SQL 2K and .NET 2003.
If you need more explanation please let me know.
Thanks.
P.S.: I hope this is the right group for my query.Hi SQL Newbie,
Have you considered using a linked server? Then you can drop the table each
month and use Select ... Into SomeTable.
Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
To set up the linked server you can use:
Use Master
Go
EXEC master.dbo.sp_addlinkedserver
@.server = N'MyLinkedServer',
@.srvproduct=N'Visual FoxPro 9',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\MyPath\MyDirectory\"',
@.provstr=N'VFPOLEDB.1'
To select data you can use this as your command text:
Drop Table Parcel
- followed by -
Select * Into Parcel From MyLinkedServer...Parcel -- 3 dots are required.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
news:28349691-2D45-43FE-9B19-0DB798CB0D6F@.microsoft.com...
> Hi,
> I need to import dbf file into sql server. This dbf file is about 300 MB
> with a 250,000 records. The import process needs to happen once a month.
> I can create a DTS package and schedule it but the problem is that the
> structure of DBF file (i.e. no. of columns) changes.
> So, my solution was to:
> 1.) Read the schema of dbf file.
> 2.) Truncate and drop the table if it exist.
> 3.) Create table accordingly to the schema read in step 1.
> 4.) Output the dbf file to an xml file. (This step fails).
> 5.) Read the xml file. (never reaches this step).
> 6.) Create an index for a column that is going to be used for searching
> records. (Hopefully will reach to this step).
> Following is my code for step 4:
> Dim strSql, strMsg As String
> Dim myConnection As New OleDbConnection(m_strDBFConnection)
> myConnection.Open()
> Dim myCommand As New OleDbCommand(strSql, myConnection)
> myCommand.CommandText = "Select Parcel.* From Parcel"
> Dim myAdapter As New OleDbDataAdapter
> myAdapter.SelectCommand = myCommand
> Dim myDS As New DataSet
> myAdapter.Fill(myDS) 'Take a long time here. In sql server it
> take about 2 minutes to read th parcel table.
> myDS.WriteXml("C:\parcel.xml")
> I am using SQL 2K and .NET 2003.
> If you need more explanation please let me know.
> Thanks.
> P.S.: I hope this is the right group for my query.|||Hello Cindy,
The main reason for me doing all these was to let the customer decide when
he wants newer data in the database. My goal was to automate the whole
process and let customer do it with just a button click. The button click
would do the steps I mentioned in my initial post i.e.
> 1.) Read the schema of dbf file.
> 2.) Truncate and drop the table if it exist.
> 3.) Create table accordingly to the schema read in step 1.
> 4.) Output the dbf file to an xml file. (This step fails).
> 5.) Read the xml file. (never reaches this step).
> 6.) Create an index for a column that is going to be used for searching
> records. (Hopefully will reach to this step).
I cannot use linkedserver as I will have to drop it each month and I want to
let customer handle this.
But just now I got an idea. Please let me know what you think about it. It
is as follows:
Whenever the customer wants to new data I will just drop the existing linked
server and create a new one using sp_addlinkedserver.
Then, use this newly linked server for my applications.
Thanks for your response.
"Cindy Winegarden" wrote:

> Hi SQL Newbie,
> Have you considered using a linked server? Then you can drop the table eac
h
> month and use Select ... Into SomeTable.
> Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
> downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
> To set up the linked server you can use:
>
> Use Master
> Go
> EXEC master.dbo.sp_addlinkedserver
> @.server = N'MyLinkedServer',
> @.srvproduct=N'Visual FoxPro 9',
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\MyPath\MyDirectory\"',
> @.provstr=N'VFPOLEDB.1'
> To select data you can use this as your command text:
> Drop Table Parcel
> - followed by -
> Select * Into Parcel From MyLinkedServer...Parcel -- 3 dots are required.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
> news:28349691-2D45-43FE-9B19-0DB798CB0D6F@.microsoft.com...
>
>|||Hi Newbie,
The linked server is not the same as linking a table in Microsoft Access.
It's basically a permanent OLE DB connection to a data directory where the
DBFs are located. The linked server does not need to know the names or
structures of the tables it is linked to. Each time it's used it will look
in the directory it points to to find the table referenced in the T-SQL
Select (etc.) command.
Why don't you just write an SQL stored procedure to select from the source
table into an SQL Server table directly? It will create the SQL table for
you without your needing to know the table schema up front. Part of the
stored procedure can be to index whichever column you need to work with.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
news:5FD8DCF4-80C2-4A1D-923A-60A22F1676AF@.microsoft.com...
> Hello Cindy,
> The main reason for me doing all these was to let the customer decide when
> he wants newer data in the database. My goal was to automate the whole
> process and let customer do it with just a button click. The button click
> would do the steps I mentioned in my initial post i.e.
> I cannot use linkedserver as I will have to drop it each month and I want
> to
> let customer handle this.
> But just now I got an idea. Please let me know what you think about it. It
> is as follows:
> Whenever the customer wants to new data I will just drop the existing
> linked
> server and create a new one using sp_addlinkedserver.
> Then, use this newly linked server for my applications.|||Hello Cindy,
Your solution takes me back to my original post where I wanted to write the
dbf file to xml file and then read back into a table in SQL Server. If I am
not able to write the dbf file to xml file than the only solution for me
would be to iterate through the quarter million record and insert them
individually. I am looking for a more efficient way to import dbf file into
sql server either using stored procedures or vb.net or combination of both.
Your help is very much appreciated.
Thanks & Regards.
"Cindy Winegarden" wrote:

> Hi Newbie,
> The linked server is not the same as linking a table in Microsoft Access.
> It's basically a permanent OLE DB connection to a data directory where the
> DBFs are located. The linked server does not need to know the names or
> structures of the tables it is linked to. Each time it's used it will look
> in the directory it points to to find the table referenced in the T-SQL
> Select (etc.) command.
> Why don't you just write an SQL stored procedure to select from the source
> table into an SQL Server table directly? It will create the SQL table for
> you without your needing to know the table schema up front. Part of the
> stored procedure can be to index whichever column you need to work with.
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
> news:5FD8DCF4-80C2-4A1D-923A-60A22F1676AF@.microsoft.com...
>
>|||Hi Newbie,
I think you're missing something when you read my posts. There is NO need
for XML.
I assume you will get the name of the DBF to import from a textbox on your
VB app's form. In your VB app you will execute SQL that calls a stored
procedure with a parameter of the name of the DBF.
I'm assuming the DBFs will always be in the same directory, so create a
Linked Server on the server and point it to the directory where the DBFs
will be kept. Assuming you have installed the VFP OLE DB data provider, you
can follow this example:
-- ----
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFPTest',
@.srvproduct=N'Your description here',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\Temp\"',
@.provstr=N'VFPOLEDB.1'
-- ----
Write an SQL stored procedure that takes the name of the new file as a
parameter.
-- ----
Create Procedure dbo.spImportData
@.TableName VarChar(10)
As
Declare @.SelectCommand VarChar(100)
-- Delete existing table
If Exists
(Select * From Sys.Objects Where Object_ID =
Object_ID(N'dbo.VFPImport') And Type In (N'U'))
Drop Table dbo.VFPImport
-- Import new data
Select @.Selectcommand =
'Select * Into dbo.VFPImport From VFPTest...' + @.TableName
Exec (@.Selectcommand)
Go
-- ----
Here's some VB code to test it all out:
-- ----
Imports System
Imports System.Data
Imports System.Data.OleDb
Module Module1
Sub Main()
Try
'-- Make some VFP data to play with
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Create Table TestImport (Field1 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestImport Values ('HelloWorld')", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
'-- Connect to the SQL Server to run your stored proc
Dim cn2 As New SqlClient.SqlConnection( _
"Data Source = C-K-Winegarden;Initial Catalog = Test;" + _
"Integrated Security = True;")
cn2.Open()
'-- Assume you'll be getting tableName from a TextBox on a form
Dim tableName As New String("TestImport")
Dim cmd3 As New SqlClient.SqlCommand( _
"Exec dbo.spImportData @.TableName = " + tableName, cn2)
cmd3.ExecuteNonQuery()
cn1.Close()
cn2.Close()
Catch e As Exception
MsgBox(e.ToString())
End Try
End Sub
End Module
-- ----
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"SQL Newbie" <SQLNewbie@.discussions.microsoft.com> wrote in message
news:013AF82C-60AC-45E0-927B-5EBFB321BE69@.microsoft.com...
> Hello Cindy,
> Your solution takes me back to my original post where I wanted to write
> the
> dbf file to xml file and then read back into a table in SQL Server. If I
> am
> not able to write the dbf file to xml file than the only solution for me
> would be to iterate through the quarter million record and insert them
> individually. I am looking for a more efficient way to import dbf file
> into
> sql server either using stored procedures or vb.net or combination of
> both.

Import and Export Wizard: Did they try it out before they shipped it!!!?

I have an excel file that, at least to me, is pretty simple. It's got 8 tables with about 25 records each. I'm trying to import 2 of those tables. Everytime I try to import I get this message:

Column "Name" cannot convert between unicode and non-unicode string data types.

So I've tried every combination of import/export that I can think of here and stil I get this message. Now I really don't think that I have any oddball characters in my data. As a matter of fact there isn't even any punctuation in there. And yet, SQL thinks there's some unicode in there. My dest column type for Name is a varchar(max). No issues there. So this is really about the simplest sort of data transfer that I can think of.

And yet it doesn't work. Which leads me to my question: did anyone at the big M actually use this wizard before they shipped it? No really. I'm doing a very simple data transfer and I'm gonna have to resort to some custom code to import my data? I've successfully imported data into the database, but I had to do it one table at a time (csv), because of the above unicode issue. I have foreign key constraints here, so I'm gonna have to import all this data in a very specific order, one table at a time.

One really absurd oversight is the names of the tables. Excel seems to save table names with a $ after. But the wizard doesn't seem to understand that. So every time I have to tell it that the Company$ table in excel actually needs to point to the Company table in SQL. Huh.

I could've saved myself some time if that I/O wizard just wasn't there.

One more thing, did they think that I might want to import more than one table to the same database via a csv file? No, they didn't because the wizard, once completed, only gives you the option of exiting. You can't hit "Back" and do the same thing with a different flat file. You have to restart the wizard and re-connect to the database etc. etc. Start from scratch. So I'm wasting even more time because of this thing.

|||

Wait, it gets worse!!!

Now I'm trying to export the database to an excel file and I'm getting the same message!

How can an export wizard not know how to export varchar fields? Possibly the second most common field type, right? No, they aren't nvarchars, they're varchars.

Now I'm gonna have to custom code this ***. Just so I can export a freakin varchar field.

|||

Can you supply some more specifics? I'm not exactly a MS lover, but I have used the Import/Export wizard on a wide variety of data sources and have seldom been totally stymied. I have encountered some difficulties with Excel imports, mostly around fields being truncated due to formatting.

Can you isolate the data that it is barfing on? It is also possible to specify the output type. You can also adjust the query used to select the data.

Repeating an import/export is also a pet peave of mine, but it is possible to save the definition as a DTS package and get reuse there. Also, the list boxes are good at remembering your previous choices, so the repetition can be slightly less tedious.

Like I said, I'm usually the first to bash the products that MS has "discovered" from others, but Import/Export is a place I usually give them their props.

|||

Here's a list of more problems I'm encountering:

- wizard is locking the excel file so that I can't edit it, sometimes locking it regardless of where I am in the process of importing (even locked on the pick a file screen sometimes)

- wizard is encountering truncation errors on fields that are not included in the import (marked as <ignore> in field mapping)

- encountering a truncation error on a column that is defined as nvarchar(max) in the database

The bottom line: I'm giving up on using this wizard with an excel file. After changing every text field in the database to nvarchar(max) I'm still not able to import fields that have a grand total of one character in them due to truncation errors.

There is simply nothing special about this data. No symbols. It's all the keys that you see on your keyboard and not even the ones above the numbers (!@.#$ etc). I've blown too much time trying to get this wizard to do something extremely simple.

|||

anomolous wrote:

Can you isolate the data that it is barfing on? It is also possible to specify the output type. You can also adjust the query used to select the data.

Repeating an import/export is also a pet peave of mine, but it is possible to save the definition as a DTS package and get reuse there. Also, the list boxes are good at remembering your previous choices, so the repetition can be slightly less tedious.

Like I said, I'm usually the first to bash the products that MS has "discovered" from others, but Import/Export is a place I usually give them their props.

It's barfing on any text field.

I think the DTS package save will only work for specific values, right? Like I'd really like to be able to just hit <BACK> a few times, change the flat file that I'm using then hit forward and so on. Without having to re-select the db and password. Of course this would all be a moot point if I could get the freakin excel import to work.

Reply if you want to, but I've decided using this wizard to import an excel file is a waste time. Now I get to use flat files, which means I have to go through this wizard 12 freakin times.

And this textbox sucks too. I just inserted a smily and it deleted half of a paragraph!

|||Don't use the excel import unless you have a lot of time on your hands, that's the answer!

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