MS SQL Server

[Dave Cole]

>>>>> "Ben" == Ben  <b.e.n. at .r.e.h.a.m.e...c.o.m> writes:

Ben> Hi, Unfortunately I have to upload data to a MS SQL server
Ben> ... Can anyone recommend a tool for doing this or docs\examples
Ben> :] ... is it ANSI SQL compliant like MySQL and therefore can use
Ben> the same commands?

Ben> greatly appreciate any advice

You don't say which platform you are using as the source for the
data...

The Sybase module which I wrote can be compiled on Linux or NT (so I
hear) and it has bulkcopy support.  In my limited experience of using
it to talk to MS SQL server I was able to bulkcopy data into tables
which did not have IDENTITY columns.  There seems to be some
difference between Sybase and MS SQL server in their handling of
that column type.

        http://www.object-craft.com.au/projects/sybase/

An example of bulkcopy from the README file:

>>> import Sybase
>>> 
>>> data = (('pencils',  5),
>>>         ('books',  300),
>>>         ('videos',  11))
>>> 
>>> db = Sybase.connect('SYBASE', 'user', 'password', bulkcopy = 1, auto_commit = 1)
>>> db.execute('create table #bogus (name varchar(40), num int)')
>>> 
>>> bcp = db.bulkcopy('#bogus)
>>> for line in data:
>>>     bcp.rowxfer(line)
>>> print 'Loaded', bcp.done(), 'rows'

The auto_commit is needed because bulkcopy does not work inside a
transaction.

Sybase 11.0.3 is a free download from http://linux.sybase.com/
It is free for development and deployment.

If your source data is CSV format then you can use another one of the
modules I wrote to load it into a format suitable to feeding the
bulkcopy object in the Sybase module.  The module was written
specifically to handle data type data produced by Access and Excel.

        http://www.object-craft.com.au/projects/csv/

The above bulkcopy example enhanced to use the csv module would look
something like this:

>>> import Sybase, csv
>>>
>>> db = Sybase.connect('SYBASE', 'user', 'password', bulkcopy = 1, auto_commit = 1)
>>> db.execute('create table #bogus (name varchar(40), num int)')
>>> 
>>> p = csv.parser()
>>> bcp = db.bulkcopy('#bogus')
>>> for line in open('datafile').readlines():
>>>     fields = p.parse(line)
>>>     if fields:
>>>         bcp.rowxfer(line)
>>> print 'Loaded', bcp.done(), 'rows'

- Dave

P.S.  Ob.Vapour #1: I have some more appropriate software under
      development but do not want to announce it until it is ready...

      Ob.Vapour #2: I just tested the above and realised that I had
      not added support for the optional bulkcopy = 1 argument.  I
      have since fixed that and it will be in the next release.  Two
      releases in two days - oops.

-- 
http://www.object-craft.com.au