Importing Records to SQL Server

[Trevor Best]

I have an import routine that takes a generic file (Excel, CSV, Fixed
length, PDMS BOM, XML, etc) and maps fields to a temp import table on
the server, in the field mapping there may be functions specified so the
user may put in "Mid([DocNumber,12,3)" as a value to go into FieldX.

I've tried 3 methods of getting the data from a linked table (CSV file)
to the SQL Server, each having some drawbacks.

Method 1: DAO, pokes each record in one at a time.
Drawback: Extremely slow (17.5 minutes for 76,000 rows)
Drawback2: Took a helluva lot more code and time to write than methods 2
and 3 so if it were a person I'd have thumped it by now :-).

Method 2: Build a query and execute with .Execute method
Quick (2.5 minutes for 76,000 rows)
Drawback: No visual feedback, looks like PC hung for 2.5 minutes.

Method 3: As 2 but .RunSQL method.
As quick as 2 and a progress bar to boot.
Drawback: Errors are either vague (e.g. "inserted null into field that
is not variant type" ok but which field?) or handled by DoCmd and not by
my program (e.g. "x records not appended due to key violation") as vague
as the first with the added headache that the first my program will know
about it is if the user cancels then the error message is that the user
cancelled, which doesn't tell the program what was wrong with the data.

Methods 1 & 2 give concise error messages, e.g. "Cannot insert null into
column xyz, insert fails", which is more helpful.

I must say I was surprised at how much faster the query method was to
DAO, I knew it would be faster but not that much difference!

Before I investigate anything else as a viable alternative to
transferring records (and manipulating them en route) I wondered if
anyone else had any ideas or (better still) experience of a better way.

One way I thought of was to build singular SQL statements rather than
the batch statement in methods 2 & 3 then execute batches of the singlar
inserts, perhaps all at once or in batches of 1000 or something (not
sure how much SQL I can poke into the server for execution in one go).
--
This sig left intentionally blank

[Tom van Stiphout]

On Thu, 25 Nov 2004 20:56:28 +0000, Trevor Best <no****@besty.org.uk>
wrote:

SQL Server has Data Transformation Services available for import and
export. Read up on it in Books Online. DTS can be invoked
programmatically.

-Tom.

I have an import routine that takes a generic file (Excel, CSV, Fixed
length, PDMS BOM, XML, etc) and maps fields to a temp import table on
the server, in the field mapping there may be functions specified so the
user may put in "Mid([DocNumber,12,3)" as a value to go into FieldX.

I've tried 3 methods of getting the data from a linked table (CSV file)
to the SQL Server, each having some drawbacks.

Method 1: DAO, pokes each record in one at a time.
Drawback: Extremely slow (17.5 minutes for 76,000 rows)
Drawback2: Took a helluva lot more code and time to write than methods 2
and 3 so if it were a person I'd have thumped it by now :-).

Method 2: Build a query and execute with .Execute method
Quick (2.5 minutes for 76,000 rows)
Drawback: No visual feedback, looks like PC hung for 2.5 minutes.

Method 3: As 2 but .RunSQL method.
As quick as 2 and a progress bar to boot.
Drawback: Errors are either vague (e.g. "inserted null into field that
is not variant type" ok but which field?) or handled by DoCmd and not by
my program (e.g. "x records not appended due to key violation") as vague
as the first with the added headache that the first my program will know
about it is if the user cancels then the error message is that the user
cancelled, which doesn't tell the program what was wrong with the data.

Methods 1 & 2 give concise error messages, e.g. "Cannot insert null into
column xyz, insert fails", which is more helpful.

I must say I was surprised at how much faster the query method was to
DAO, I knew it would be faster but not that much difference!

Before I investigate anything else as a viable alternative to
transferring records (and manipulating them en route) I wondered if
anyone else had any ideas or (better still) experience of a better way.

One way I thought of was to build singular SQL statements rather than
the batch statement in methods 2 & 3 then execute batches of the singlar
inserts, perhaps all at once or in batches of 1000 or something (not
sure how much SQL I can poke into the server for execution in one go).

[Trevor Best]

Tom van Stiphout wrote:

On Thu, 25 Nov 2004 20:56:28 +0000, Trevor Best <no****@besty.org.uk>
wrote:

SQL Server has Data Transformation Services available for import and
export. Read up on it in Books Online. DTS can be invoked
programmatically.

This will be generic and the only app I know will definately be on the
workstation running it will be Access. Wouldn't DTS require the SQL
Server tools to be installed? This may not be the case in most
circumstances.

--
This sig left intentionally blank

[John Winterbottom]

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...

I have an import routine that takes a generic file (Excel, CSV, Fixed
length, PDMS BOM, XML, etc) and maps fields to a temp import table on the
server, in the field mapping there may be functions specified so the user
may put in "Mid([DocNumber,12,3)" as a value to go into FieldX.

Before I investigate anything else as a viable alternative to transferring
records (and manipulating them en route) I wondered if anyone else had any
ideas or (better still) experience of a better way.

Have you looked at BULK INSERT or DTS? If you're after raw performance these
would probably be your best options. If you need more flexinbiltiy than
straight bulk insert you can use E-M to design an initail DTS package, with
any transformations you need then save it as a VB module from where you can
edit it to suit. Try a test using the DTS wizard and see what kind of
performance you get.

[Tom van Stiphout]

On Thu, 25 Nov 2004 21:53:59 +0000, Trevor Best <no****@besty.org.uk>
wrote:

I believe DTS indeed requires the tools to be installed locally. If
you can't be sure of that, this may not be a solution for you. Unless
you want to use DCOM to the server - not my first choice...

-Tom.

Tom van Stiphout wrote:

On Thu, 25 Nov 2004 20:56:28 +0000, Trevor Best <no****@besty.org.uk>
wrote:

SQL Server has Data Transformation Services available for import and
export. Read up on it in Books Online. DTS can be invoked
programmatically.

This will be generic and the only app I know will definately be on the
workstation running it will be Access. Wouldn't DTS require the SQL
Server tools to be installed? This may not be the case in most
circumstances.

[Trev]

John Winterbottom wrote:

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...

I have an import routine that takes a generic file (Excel, CSV, Fixed
length, PDMS BOM, XML, etc) and maps fields to a temp import table on the
server, in the field mapping there may be functions specified so the user
may put in "Mid([DocNumber,12,3)" as a value to go into FieldX.

Before I investigate anything else as a viable alternative to transferring
records (and manipulating them en route) I wondered if anyone else had any
ideas or (better still) experience of a better way.

Have you looked at BULK INSERT or DTS? If you're after raw performance these
would probably be your best options. If you need more flexinbiltiy than
straight bulk insert you can use E-M to design an initail DTS package, with
any transformations you need then save it as a VB module from where you can
edit it to suit. Try a test using the DTS wizard and see what kind of
performance you get.

I'm finding DTS difficult to use, the (CSV) file I want to import is in
my temp directory. For .txt/csv DTS requires a DSN and even prompts for
username and password although not got that far yet as setting up the
DSN brings up an old 16 bit looking Open File Dialog that won't allow me
to browse to the temp directory because it's hidden (well "Local
Settings" above it is hidden) and typing that or %TEMP% won't get me
there either. I've never been impressed with DTS' ability to copy one
SQL Server database to another anyway, so why would I expect success out
of it for this? :-)

I think I might abandon DTS as a tool as the import started out as a
self contained tool within the access front end would now extend to
having to have SQL Server tools installed and have a DSN. I'd rather
keep it in-house as it were. I don't want to have to rely on users
having other applications or settings on their machine that are beyond
my control as that leads to my program not working because they haven't
got all the other bits. Fair enough but in the user's eyes it's just "my
program doesn't work" so of course it's my fault and my name gets
dragged in the mud, and mud sticks no matter how much it wasn't supposed
to be on there in the first place.

[John Winterbottom]

"Trev@Work" <no.email@please> wrote in message
news:41***********************@news.easynet.co.uk. ..

I think I might abandon DTS as a tool as the import started out as a self
contained tool within the access front end would now extend to having to
have SQL Server tools installed and have a DSN. I'd rather keep it
in-house as it were.

You might want to consider straight BULK INSERT then. We have an application
that periodically receives text files via ftp and inserts the data into
several tables. We also use temporary staging tables, because some data will
aleady exist and some won't. If the data already exists we update it, if
not we insert the new records. Everything is done on the server in a single
stored procedure. Here's a code snippet that shows how we do it - this loads
data from a temp file called clients.txt into a table called temp_clients,
then inserts the data into the main clients table. Also we use dynamic SQL
so that we can change the name of the text file:

/* code starts */
declare @file nvarchar(25),
@strSQL nvarchar(1000)

-- first get files from ftp server
-- getData.BAT is a batch file which copies the files to the local
machine
exec master.dbo.xp_cmdshell 'c:\getData.BAT, NO_OUTPUT

set @path = 'c:\'

-- drop primary key before bulk load to speed things up
if exists
(
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_NAME = 'PK_temp_clients'
)
alter table dbo.temp_clients drop constraint PK_temp_clients
if (@@ERROR<>0) goto on_error

-- purge old records
truncate table dbo.temp_clients
if (@@ERROR<>0) goto on_error

set @file = 'temp_clients'
set @strSQL = bulk insert ' + db_name() + '.dbo.' + @file
+ ' from''' + @path + @file + '.txt'' with '
+ '(FIELDTERMINATOR = ''|'')'

exec sp_executesql @strSQL
if (@@ERROR<>0) goto on_error

-- add primary key back
alter table dbo.temp_clients add constraint PK_temp_clients primary key
(ClientID)

-- update matching rows
update dbo.clients set
acctNumber = t.acctNumber, lastName = t.lastName,
firstName = t.firstName, salutation = t.salutation,
business = t.business, address = t.address
from dbo.temp_clients as t where t.ClientID = clients.ClientID
if (@@ERROR<>0) goto on_error
-- add new clients
insert into dbo.clients (ClientID, acctNumber, lastName,
firstName, salutation, business, address)
select ClientID, acctNumber, lastName,
firstName, salutation, business,
from dbo.temp_clients t
where not exists
(
select * from dbo.clients c
where c.ClientID = t.ClientID
)
if (@@ERROR<>0) goto on_error

/* code ends */
This has been running well for a couple of years now. The client machine,
(located remotely), has a VB app that periodically exports data from their
local Jet database to text files, ftp's them to our ftp server, then calls
the procedure above. We call it asynchronously using sql agent so the client
can make the connection, start the procedure and then disconnect.

[Trevor Best]

John Winterbottom wrote:

You might want to consider straight BULK INSERT then. We have an application
that periodically receives text files via ftp and inserts the data into
several tables. We also use temporary staging tables, because some data will
aleady exist and some won't. If the data already exists we update it, if
not we insert the new records. Everything is done on the server in a single
stored procedure. Here's a code snippet that shows how we do it - this loads
data from a temp file called clients.txt into a table called temp_clients,
then inserts the data into the main clients table. Also we use dynamic SQL
so that we can change the name of the text file:

Thanks for the code but I've already written the stored procedures, one
for checking the data and generating a delta if it's a cumulative file,
the other updates the main tables using the delta to apply +/-
quantities, other updates, inserts and deletes. This all works OK and in
the blink of an eye. I then archive off the delta that's been applied as
this may be required by my client's client, particularly for MTOs.

The problem with bulk insert is that it will expect the source table to
be in the same format as the target table yes? If only my clients knew
how to use their CAD systems and ISOGEN properly <g> Also does the user
require special privvies to run extended sps like xp_cmdshell?

I might look into this if I find nothing better but I will have to do
some field mapping either client or server side. ATM my temp holding
table on the server has some required fields, these are the ones mapped
to the live tables that are required. I made them required in the temp
table so I could weed out any errors before attempting to update the
live tables but I suppose I could do a soft check for required fields
thus eliminating the only error I can think of that will occur during
the transfer from import file to temp table. Having said that I can
think of data type mismatches that may occur but as I've typed the
previous sentence now I can't be arsed to delete it :-)

I might stick with methods 2 and 3 (the query methods) and give the user
a choice to have either a nice progress bar or a meaningful error
message. I did start looking at the batch of inserts with value lists
but found where in my import spec I had something defined like
Mid(field,2,1) I would have to either evaluate it locally or substitute
for the equivalent SQL Server function. I then found it took slightly
longer than the query methods and I hadn't even put the code in to throw
the SQL statements at the server.
--
This sig left intentionally blank