Suyati Technologies
  • Services
    • Custom Development
    • Salesforce
    • Analytics
    • Enablers
      • RPA
      • Cloud
      • Testing as a Service
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us
Suyati Technologies
  • Services
    • Custom Development
    • Salesforce
    • Analytics
    • Enablers
      • RPA
      • Cloud
      • Testing as a Service
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us
Suyati Technologies > Blog > Multiple Record Insertions

Multiple Record Insertions

by Team Suyati July 23, 2012
by Team Suyati July 23, 2012 0 comment

For some situations, we need to insert multiple records into SQL tables.  In normal case, we do record insertions one by one. Most of our application designs have separate procedures for update and delete on the DB side, and the application invokes these procedures every time it needs to insert a record. This will affect the performance of the system as there would many DB calls originating from the application. From an enterprise perspective, DB calls are very expensive. Any system we design which has DB interactions should have minimal amount of DB calls.
Here, we explore the ways on how to handle the multiple DML operations and with multiple SQL statements. It is at this point that we need to remember our greatest B2B data management technique known as “XML”!

Analysis

Let’s try to insert more than one record into a table.
Suppose the table name is ‘Test’, and the fields are: – ID, Name, Address, Place. Then a possible way of xml would be as shown below.

     

  1. <TestList>
  2. <Test>
  3. <Insert>
  4. <ID>1</ID>
  5. <Name>Name 1</Name>
  6. <Address>Address 1</Address>
  7. <Place>Place 1</Place>
  8. </Insert>
  9. </Test>
  10. <Test>
  11. <Insert>
  12. <ID>2</ID>
  13. <Name>Name 2</Name>
  14. <Address>Address 2</Address>
  15. <Place>Place 2</Place>
  16. </Insert>
  17. </Test>
  18. </TestList>

  19.  

Here ‘TestList’ is representing the List of records and ‘Test’ represents Table name. The ‘Insert’ tag is identifying these records for Insert purpose. Now, let’s pass this whole XML as the parameter into the procedure.
The core logic of the procedure is

     

  1. INSERT INTO Test(ID,Name,Address,Place)
  2. SELECT    ID,Name,[Address],Place
  3. FROM OPENXML( @hdoc, 'TestList/Test/Insert')
  4. WITH (
  5. ID            int            'ID',
  6. Name        varchar(100)    'Name',
  7. Address        varchar(100)    'Address',
  8. Place        varchar(50)        'Place'
  9. )

  10.  

Here in OPENXML keyword is used for opening the XML string. Here the data for insertion is inside the following path ‘TestList/Test/Insert’. The actual data elements under this path are ID, Name, Address and Place. The next step in the SQL query is to assign the table fields to XML tag names. i.e.,

ID int ‘ID’

After associating each of these elements with table fields, we are done with the logic of insert stored procedure. Now we can execute the procedure with xml string. It will insert multiple records in a single Insert statement.

This method illustrates how we could insert multiple data with just one DB call for insertion. Pretty neat!
We can use the same logic explained above also for SQL table data updations and data deletions. But in “update” we need to include the Condition. For this, we can use the Aliasing names.
And the main advantage of this procedure is that we can include more than one DML operation.
The example is :-

XML String: (Acts as Input Parameter for the Stored Procedure)

     

  1. '<TestList>
  2. <Test>
  3. <Insert>
  4. <ID>1</ID>
  5. <Name>Name 1</Name>
  6. <Address>Address 1</Address>
  7. <Place>Place 1</Place>
  8. </Insert>
  9. </Test>
  10. <Test>
  11. <Insert>
  12. <ID>2</ID>
  13. <Name>Name 2</Name>
  14. <Address>Address 2</Address>
  15. <Place>Place 2</Place>
  16. </Insert>
  17. </Test>
  18. <Test>
  19. <Update>
  20. <ID>1</ID>
  21. <Name>Name 3</Name>
  22. <Address>Address 3</Address>
  23. <Place>Place 3</Place>
  24. </Update>
  25. </Test>
  26. <Test>
  27. <Update>
  28. <ID>2</ID>
  29. <Name>Name 4</Name>
  30. <Address>Address 4</Address>
  31. <Place>Place 4</Place>
  32. </Update>
  33. </Test>
  34. <Test>
  35. <Delete>
  36. <ID>1</ID>
  37. </Delete>
  38. </Test>
  39. </TestList>'

  40.  

The procedure (Which Does the Magic)

     

  1. Create PROCEDURE [dbo].[InsertTest]
  2. (
  3. @doc     XML
  4. )
  5. AS
  6. BEGIN
  7. Declare @hdoc int
  8. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
  9. INSERT INTO Test
  10. (
  11. ID,
  12. Name,
  13. Address,
  14. Place
  15. )
  16. SELECT        ID,
  17. Name,
  18. [Address],
  19. Place
  20. FROM OPENXML( @hdoc, 'TestList/Test/Insert')
  21. WITH (
  22. ID            int                'ID',
  23. Name            varchar(100)    'Name',
  24. Address        varchar(100)    'Address',
  25. Place        varchar(50)        'Place'
  26. )
  27. UPDATE Test SET
  28. Name = A.Name
  29. Address = A.Address,
  30. Place = A.Place
  31. FROM OPENXML( @hdoc, 'TestList/Test/Update')
  32. WITH (
  33. ID            int                'ID',
  34. Name            varchar(100)    'Name',
  35. Address        varchar(100)    'Address',
  36. Place        varchar(50)        'Place'
  37. )A
  38. INNER JOIN Test B
  39. on B.ID = A.ID
  40. DELETE FROM Test
  41. FROM OPENXML( @hdoc, 'TestList/Test/Delete')
  42. WITH (
  43. ID        int        'ID'
  44. )A
  45. WHERE A.ID = Test.ID
  46. END

  47.  

Hope you techies out there like this idea of handling multiple DML statements and data management in one stored procedure.
Let me know your thoughts!! J

0 comment
0
FacebookTwitterLinkedinTumblr
previous post
Windows 8: A Disaster or a Game Changer?
next post
A shift in Relational Data Storage Paradigms – Data storage on the Cloud?

You may also like

Einstein Analytics & Salesforce CRM Empowering Sales Teams

February 7, 2023

Resolve Cases Easily with Interactive Einstein Search Answers

February 7, 2023

2023 AI Landscape: Key Developments

February 7, 2023

An analytical breakdown of DataOps and its core...

January 5, 2023

Watch Out for These 5 Cloud Computing Trends...

January 5, 2023

How Artificial Intelligence is Improving Customer Experience

January 5, 2023

Top 10 Strategic Technology Trends for 2023

January 4, 2023

How can Enterprises Benefit from DevOps?

January 4, 2023

Leveraging DevOps Capabilities in the Telecom Sector

January 3, 2023

Know more about the Salesforce Winter ’23 Release...

December 12, 2022

Leave a Comment Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

Keep in touch

Twitter Linkedin Facebook Pinterest

Recent Posts

  • Einstein Analytics & Salesforce CRM Empowering Sales Teams

    February 7, 2023
  • Resolve Cases Easily with Interactive Einstein Search Answers

    February 7, 2023
  • 2023 AI Landscape: Key Developments

    February 7, 2023

Categories

  • Twitter
  • Linkedin
  • Facebook
  • Instagram
  • Services
    • Custom Development
    • Salesforce
    • Analytics
    • Enablers
      • RPA
      • Cloud
      • Testing as a Service
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us

© 2022 Suyati Technologies


Back To Top
Suyati Technologies

Popular Posts

  • MongoDB vs. Couchbase

    May 26, 2013
  • 2

    What is Salesforce CRM and What Does it Do?

    February 19, 2014
  • 3

    A step-by-step guide to configuring emails in MS Dynamics CRM

    April 27, 2017
  • 4

    Top 10 ETL Tools for Salesforce Data Migration 2021

    January 4, 2021
  • 5

    Steps to Migration – Salesforce Classic to Lightning

    September 1, 2021
© 2022 Suyati Technologies

Read alsox

Employing Big Data to predict deaths in Game of Thrones

September 19, 2016

Robotic Process Automation & Artificial Intelligence: Built for Each Other

October 30, 2020

2019 top trends in Digital Transformation that you need to...

December 21, 2018
Suyati Logo

Leaving So Soon?

Know more about business technology platforms for your enterprise.

Thank you!

We're glad to have you as part of our community. You'll start receiving updates shortly. Please feel free to contact us anytime with feedback or suggestions.

By continuing to use this website you agree with our use of cookies. Read More Agree