Suyati Technologies
  • Services
    • Salesforce Services
      • Sales Cloud
      • Service Cloud
      • Marketing Cloud
      • Einstein
      • Experience Cloud
      • Mulesoft
      • Commerce cloud
      • Finance cloud
      • CPQ
      • Consultation
      • Implementation
      • Integration
      • Custom Development
      • Salesforce DevOps
      • Support & Maintenance
      • App Development
      • Managed Services
    • IT Services
      • Content Management Services
      • Analytics
      • RPA
      • Front end Technologies
      • Microsoft Applications
      • Cloud
      • DevOps
      • Snowflake
  • Approach
    • Development Methodology
    • Engagement Model
    • Consulting
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us
Suyati Technologies
  • Services
    • Salesforce Services
      • Sales Cloud
      • Service Cloud
      • Marketing Cloud
      • Einstein
      • Experience Cloud
      • Mulesoft
      • Commerce cloud
      • Finance cloud
      • CPQ
      • Consultation
      • Implementation
      • Integration
      • Custom Development
      • Salesforce DevOps
      • Support & Maintenance
      • App Development
      • Managed Services
    • IT Services
      • Content Management Services
      • Analytics
      • RPA
      • Front end Technologies
      • Microsoft Applications
      • Cloud
      • DevOps
      • Snowflake
  • Approach
    • Development Methodology
    • Engagement Model
    • Consulting
  • 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

What you need to know before kick-starting cloud...

January 13, 2022

An Eye-opener into the Future Trends in Salesforce...

January 13, 2022

Seven Key IT Outsourcing Trends to Expect in...

January 13, 2022

How to Select the Right Partner for a...

January 13, 2022

On Premises vs Cloud CRM: Which is Better?

September 28, 2021

Choosing between Cloud and On-Premise Servers for your...

September 28, 2021

Broken Customer Experience? What’s the Fix?

August 19, 2020

Are Remote Proctored Exams a New Reality?

August 18, 2020

10 Exciting Features in Salesforce’s new Summer ’20...

August 17, 2020

Importance of Data Analytics in Developing Smart Cities

August 11, 2020

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

  • What you need to know before kick-starting cloud implementation

    January 13, 2022
  • An Eye-opener into the Future Trends in Salesforce Commerce Cloud

    January 13, 2022
  • Seven Key IT Outsourcing Trends to Expect in 2022

    January 13, 2022

Categories

  • Twitter
  • Linkedin
  • Facebook
  • Instagram
  • Services
    • Salesforce Services
      • Sales Cloud
      • Service Cloud
      • Marketing Cloud
      • Einstein
      • Experience Cloud
      • Mulesoft
      • Commerce cloud
      • Finance cloud
      • CPQ
      • Consultation
      • Implementation
      • Integration
      • Custom Development
      • Salesforce DevOps
      • Support & Maintenance
      • App Development
      • Managed Services
    • IT Services
      • Content Management Services
      • Analytics
      • RPA
      • Front end Technologies
      • Microsoft Applications
      • Cloud
      • DevOps
      • Snowflake
  • Approach
    • Development Methodology
    • Engagement Model
    • Consulting
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us

© 2021 Suyati Technologies


Back To Top
Suyati Technologies

Popular Posts

  • 1

    What are the Top 3 risks for implementing a CX Program?

    August 30, 2019
  • 2

    Do you need a separate CX Team at your company?

    September 2, 2019
  • 3

    How to build Employee Advocacy for your Business?

    September 3, 2019
  • 4

    What is Salesforce CRM and What Does it Do?

    February 19, 2014
  • 5

    Tips to Reduce Salesforce Pricing

    February 17, 2015
© 2021 Suyati Technologies

Read alsox

Making the right investment in your Transformational Projects

September 5, 2012

How Machine Learning and AI can enhance the retailer interaction...

February 28, 2018

Is Multicloud the Future of Enterprise IT?

October 29, 2018

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