Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-10-01 : 08:51:13
|
| I'm trying to make a design decision and I don't really have enough experience to do it properly so would appreciate some advice.I have written sprocs that insert a single record into key tables in my d/b and ensure all the required integrity is in place. I'm now in a position where this will need to be called by another system so that data can be put into my d/b. This other system will be wanting to put 1 or more records into my system on each call.My questions are:-1) Is there some set based way of dealing with this i.e. should I have some sort of sproc that inserts mulitple records at a time and if so can a whole recordset be passed into a sproc (and how?)2) Would I be better requesting that a single call is made for a single record that was to be inserted (seems to be less efficient though simpler to me)3) Does anyone have any advice on how to flag any errors (there is no user interface)? In fact if I return a parameter is it likely that the other system will be able to recieve them assuming that the other system is reasonably well writtenMany thankssteveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-01 : 08:58:32
|
| do you have all the necessary constraints set on your table? Can the database layer alone determine what is an error and what is not? if you have an identity as the PK on your table and that is the only unique constraint, you will have a problem.Also -- make sure if you have any triggers on this table they are written properly to handle sets of data at once. I've seen way too many triggers written out there that assume Inserted or Updated contain only 1 row at all times.- Jeff |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-11 : 06:41:07
|
| How about getting the data as XML so that it can be killed at source if it doesn't get through an XML Schema?Using this approach we "pull" data from XML into a temporary table - it basically has additional columns for a "Batch number" (so that multiple jobs can be going on concurrently), Status and Message. The data will have survived verification against XML Scema, so its clean enough to go into the database, but it might yet fail relational etc. constraints. The Status column changes from "New" to either "Processed" or "Error". The message column shows any message(s) to assist the operator in fixing the problem. Some types of jobs bounce the whole batch if one row fails, others only bounce individual records - depends on the type of job.Once a batch is fully inserted into the "transfer table" we call an SProc to do the insert into the "live table". It takes rows from that specific "batch" and inserts them - having first done any further validation checks - and then changes the Status on the transfer table as appropriate.You need some sort of housekeeping routine to delete batches after the user has extracted the error messages.There ... I've now successfully made a simple task sound complicated, and thereby justified your pay rise!Your round I believe? <g>Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-10-11 : 06:48:18
|
| Thanks KristenThat's very helpful, particularly as the data coming in will be coming as XML anyway.Beer tokens are in the post (honest guv)steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-11 : 07:09:13
|
| Have you played with "FOR XML"? That's quite a lot you can do fromt he SQL end - digesting the XML into tables; generating Schemas; that sort of stuff - but the BoL does not exactly make light-reading of it (seems like the Authors at MS didn't get to rewrite what the Techies wrote originally) ...Darn: you're still stuck on SQL7 aren't you?Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-10-11 : 13:19:16
|
I'm aware of FOR XML but to be honest I haven't really done more than take a quick look at it. Fortunately for me, this particular baby will be running on SQL Server 2k and passing the data to SQL Server 7 (it seemed a bit more secure to me to do it like that as this means that I have complete control over what happens on the 7 server). Maybe I should ask my network admin friends for advice  steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
|
|
|
Is it a Record