SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending multiple rows to the Database from an Application: Part I


Sending multiple rows to the Database from an Application: Part I

Author
Message
mohd.nizamuddin
mohd.nizamuddin
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 198
Comments posted to this topic are about the item Sending multiple rows to the Database from an Application: Part I
Daniel Macey
Daniel Macey
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 129
Good to see an article on a tricky and quite important piece of code.

Although, with SQL Server 2008 around, I am surprised that you did not mention the Table user defined type and the MERGE statement?
mohd.nizamuddin
mohd.nizamuddin
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 198
Thanks Daniel,

Although, with SQL Server 2008 around, I am surprised that you did not mention the Table user defined type and the MERGE statement?


Actually this is based on SQL Server 2005. Yes, now (in Sql Server 2008) we have a feature where we can pass table object to the SP.
Martin Wills
Martin Wills
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 Visits: 893
The technical issues are well thought out but I hope that you won't mind if I point out a couple of key spelling mistakes.

The word is delimiter and not delimeter, delimited and not delimeted. (The words are derived from the word 'limit' meaning boundary.)

You were introduced as a new author so 'welcome' and you have obviously taken care with the article.

I have only ever mastered one language so I'm full of respect for you if English is not your 'first' language. Well done.

Martin
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 169
Thank you for exploring an approach that I hadn't considered. I actually advised a colleague this month that there isn't a practical way to send bulk rows to SQL Server 2005 - he had around 100 rows to put in. But I'm particularly excited about the XML approach.

We have a 3-tier application design (rhymes with "beer"), with desktop and a server both running Java application code, as well as some 2-tier where there's just a desktop program (SQL Server is the other tier). The server is right next to the SQL Server on a fast server-room network (could even be blades), and I assume that just submitting a series of INSERT... VALUES (...) statements, with or without parameterization of the statement, is almost the best we can do. BCP and BULK INSERT won't really do, although I suppose we could do those from our Java server.

The great trap that I see with delimited data is where you want to use a delimiter character as a character in a data field. Again, XML, which is really just a sophisticated form of delimited data, comes with tools to handle that.
Darrell Bauer
Darrell Bauer
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 141
The table-valued function [dbo].[TableFormDelimetedStringWithoutNumberList] will choke on lists longer than 100 elements. Here's the error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


Using OPTION(MAXRECURSION xxx) is not allowed within a UDF, so the numbered list seems to be the only alternative for large lists. Am I missing something?
Stephen Byrne
Stephen Byrne
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 92
Good approach given the lack of alternatives, I've used the XML approach before and it can really provide a huge performance boost - but how would you handle error cases - say I'm whacking in 1000 rows and the 999th insert fails for some reason - what would you think would be the best way to get information back about the specific failure to the user?

Looking forward to part II!
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 169
I think the goal is to make it a set-based operation, so by SQL standard the error message should be as uninformative as possible ("String or binary data would be truncated"), whilst also throwing blame directly on the application developer! ;-)
LeeFAR
LeeFAR
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 322
I have used the XML approach to do some integration between databases. I would pass to an SP in a different database a set of reference numbers as an XML param and then join to that XML to the data in that database. Although this worked fine, when it started getting up to over 700 - 1000 records, I saw a major performance problem. It was very slow. Maybe it was my implementation, but I ended up having to rewrite it to eliminate the XML approach (which is what I should have done the first time, but I wanted to try out the XML approach). Just make sure you test your implementation using more data than you ever think you will need to process.



mgaldames
mgaldames
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 17
i use a pretty same script to send multiple rows in sql server 2005 with charindex but i have an issue... and was that CHARINDEX just accept 8000 chars Sad and i was sending sometimes more than 10 textareas with nvarchar(max) :__

i was thinking about the xml approach
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search