RE: SQLXMLBULKLOAD question: Does it INSERT only, or can it UPDATE as
- From: Bob <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 4 Nov 2008 02:28:00 -0800
Sabrina,
I would recommend bulk loading your data to a staging table first and then
using SQL to work out which records need to be inserted or updated. You can
use traditional LEFT JOIN, EXISTS or the EXCEPT operators to work out which
records are already there.
Also, in SQL 2008 you can use the new MERGE statement.
XSD.exe supplied with Visual Studio ( eg C:\Program Files\Microsoft Visual
Studio 8\SDK\v2.0\Bin\xsd.exe ) can create schemas, however they may not
immediately work with SQL Server. I have been opening up the Schemas in
Visual Studios visual designer to see the relationships and then adding them
manually to the xsd file based on the diagram Visual Studio shows me.
I understand there are some tools available, eg SchemaTron, XMLSpy(?) or
related tool, but I have never used them.
Hope that makes sense.
wBob
"Sabrina Veksler" wrote:
[Using assembly Interop.SQLXMLBULKLOADLib, Version=4.0.0.0, Culture=neutral,.
PublicKeyToken=null]
Hello all,
A piece of software that was built for us by a 3rd party uses
SQLXMLBULKLOADLib to import records from an XML feed into our database. The
XML feed includes previously-imported records that may or may not have
changed, as well as new records. When we run the bulkload utility, it
inserts duplicate records for those previously-imported, so to avoid this,
we have resorted to emptying out each table prior to running the bulkload
utility. This is not desirable because the tables often contain records that
were added through another source, such as the web interface.
I wonder if there isn't a way to accomplish the bulkload without dumping the
contents first? Each record contains a unique field that can be used as a
primary key, however, the original developer did not specify the field as a
primary key in the table. If I set the field as a primary key in the table,
can the bulkload be configured so that when it processes the nodes in the
XML file, it updates existing records and only inserts when the record
doesn't already exist in the table?
Because I didn't originally write this code, I would prefer to keep my
revisions to a minimum, unless there is a much better way of importing the
XML feed into our database that I can write fairly quickly.
Also, I was wondering if there is a utility to quickly generate XSD schemas
to use in the bulkload from the actual tables in SQL Server. I'd like to
modify our schemas to include the relationships and keys that I've set up
using the Management Console, but would much rather avoid doing it manually.
Please advise.
Thank you!
-Sabrina
- References:
- SQLXMLBULKLOAD question: Does it INSERT only, or can it UPDATE as well?
- From: Sabrina Veksler
- SQLXMLBULKLOAD question: Does it INSERT only, or can it UPDATE as well?
- Prev by Date: converting table data/table structure into xml file
- Next by Date: RE: Where to obtain the material to study the in-and-out of SQL Server
- Previous by thread: RE: SQLXMLBULKLOAD question: Does it INSERT only, or can it UPDATE as well?
- Next by thread: converting table data/table structure into xml file
- Index(es):
Relevant Pages
|
