question

rudrapbiswas avatar image
rudrapbiswas asked

how to batch insert using proc?

Hi All, I have got a requirement to call a proc with input parameter. The input params are the column values for a table. The params can be for multiple rows. The issue is with the performance. 1. How to send the params as bulk (xml, delimited data, ???) 2. How to handle/split the input params, break it into individual insert statement in a loop in a optimal way
stored-proceduresparameters
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seanlange avatar image
seanlange answered
You can't both loop and be optimal. They are contradictory to each other. Don't fall into the trap of cramming multiple values into a single parameter. This violates 1NF just like storing data in a similar manner does. The best option from what little detail posted would be to use table valued parameters. [ https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx][1] [ https://msdn.microsoft.com/en-us/library/bb510489.aspx][2] [1]: https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx [2]: https://msdn.microsoft.com/en-us/library/bb510489.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rudrapbiswas avatar image
rudrapbiswas answered
The issue with using TVF is, Java jdbc driver doesn't support this. I had to eliminate this option at the beginning itself. At present I have asked the Java developer to call the proc with data like: '(colval1, colval2, colval3, ...), (colval11, colval22, colval33..), ...'. This way I can avoid splitting the dataset for individual rows. I would be able to create a single insert statement like: INSERT INTO (cols...) VALUES (colval1, colval2, colval3, ...), (colval11, colval22, colval33..), .... I would like to explore some other option as well before finalizing the above one.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What it sounds like here is you are using dynamic sql now? You be better off passing in a delimited string and splitting. Here is one example of doing this. http://www.sqlservercentral.com/articles/Tally+Table/72993/ If you use a different splitter make certain it does NOT have a loop or a cursor as it will not scale well.
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
Send the XML string to your proc as varchar(max), INSERT to temp table then do set based operations from the temp table. (or skip the temp table and do direct insert into your production table if that is all you need to do) EDIT: note there is no root tag for this example to work. DECLARE @xml varchar(max) = '1234567892016-01-01 1234567802016-01-02' EXEC [dbo].[prcUpdateMailDates] @xml ------------------------------- CREATE PROC [dbo].[prcUpdateMailDates] @MailDates varchar(max) AS SET NOCOUNT ON DECLARE @MailDatesXML xml SET @MailDatesXML = @MailDates CREATE TABLE #MailDates (CardNumber bigint, MailDate date) INSERT #MailDates SELECT r.n.value('(CardNumber/text())[1]', 'bigint') [CardNumber], r.n.value('(MailDate/text())[1]', 'date') [Date] FROM @MailDatesXML.nodes('/Card')r([n]) UPDATE tmd SET MailDate = md.MailDate FROM #MailDates md JOIN dbo.tblMailDates tmd ON (tmd.CardNumber = md.CardNumber)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.