Skip to main content

Split function in SQL Server -- Method 2

There are instances when we want to insert a record into a table and based on the identity value of that row insert remaining content into another table. For the purpose of better understanding let me explain the case with an example.

Assume from a front-end page a user would provide a student's "Name", "Age' and "List of subjects he/she would be learning". Once the user clicks on "Save" button "Student Name and Age" needs to be stored in Table1 and with its identity value insert subject list in Table2. Hope I have made the case clear :)

There are actually two methods to approach this situation. They are:

1. Making use of sp_xml_preparedocument, sp_xml_removedocument and
2. Custom split function

Well, now let us take these items one by one and see how to solve this problem.

Sample table structure

Both the methods explained in this post would use the below table structure only.

Create table StudentMaster
(
StudentID int Identity(1,1) not null,
StudentName varchar(100),
StudentAge int
)
Go

Create table StudentDetails
(
StudentID int,
SubjectName varchar(10)
)

Making use of sp_xml_preparedocument and sp_xml_removedocument

Before having a look at the SP let us first have a look at important functions and system stored procs used in this approach.

In order to use and manipulate XML data in SQL Server, two new system-stored procedures and the OPENXML function have been added to SQL Server 2000.

OpenXML() functions expose XML data as a relational row set. This set enables you to insert and update the XML data in a SQL Server 2k database.

In order for SQL Server to process XML documents, we can use the system stored procedure sp_xml_preparedocument, to read the documents and verify whether it is a valid XML document or not. The stored procedure then returns a numeric handle to the XML document. That handle is passed to and used by the OPENXML function to convert the tree structure representation of the XML document to a relational format. The OpenXML() function then executes the INSERT statement to insert the data in a relational format in a SQL Server DB.

Once a document has been fully processed, we should use the sp_xml_removedocument stored procedure to reclaim the memory used by the node tree (by destroying the XML document and release the server's resources). Getting into the habit of deleting the document tree can help prevent memory shortage problems in our SQL Server application.

From SQL Server 2k there is a new function by name Scope_Identity() which returns the last identity value, produced on a connection and by a statement in the same scope.

Create procedure InsertStudent
@StudentName varchar(100),
@StudentAge int,
@SubjectString varchar(1000)
/*
Students subjects passed as XML in format:
root

----- row subjectname="ABC" studentid="123456"
----- row subjectname="DEF" studentid="123456"
/root

here, 123456 is hard-coded for replacing later in the SP and "ABC, DEF" are test vlaues; they will be changed as per the string passed
*/
As

Begin Tran StudentTransaction

Declare @NewRowId int
Declare @SubjectXmlDoc int

/* Insert Master record and get identity value */
Insert into StudentMaster (StudentName, StudentAge) Values (@StudentName, @StudentAge)

--Retreive the last Identity value inserted into the identity column (StudentID)
Select @NewRowId = Scope_Identity()

/* Replace dummy identity value with actual id value*/
Select @SubjectString = Replace(@SubjectString, '123456', Convert(varchar(10), @NewRowId))

/* XML Bulk Insert the Subjects */

-- The below line creates XML Document and returns numeric ID
Exec sp_xml_preparedocument @SubjectXmlDoc OUTPUT, @SubjectString

Insert into StudentDetails (StudentId, SubjectName) Select studentid, subjectname from OPENXML (@SubjectXmlDoc, '/root/row') With (StudentId int, SubjectName varchar(1000))

-- Deletes the XML document
Exec sp_xml_removedocument @SubjectXmlDoc

Commit Tran StudentTransaction

Hope the above stored procedure would give you a good idea of how to make use of these systems SP to solve our given problem. The only hitch with this approach is we need to pass the values as a XML format string to the SP.

Now lets have a look at the Custome Split function to solve the same issue.

Custom Split function

Create Procedure InsertStudent_Revised
@StudentName varchar(100),
@StudentAge int,

/*
Comma seperated value. A comma needs to be added at the end of the string as well.
*/

@SubjectString varchar(1000)

As

Begin Tran StudentTransaction

Declare @NewRowId int
Declare @strDelimitedString varchar(50)
Declare @Strat int
Declare @End int
Declare @strValue varchar(50)

--Insert master record and get the identity value

Insert into StudentMaster (StudentName, StudentAge) Values (@StudentName, @StudentAge)

Select @NewRowId = Scope_Identity()

--- Custom split function starts here

Set @strDelimitedString = @SubjectString
Set @Start = 1
Set @End = charindex(',',@strDelimitedString)

While (@End > 0)
Begin

Set @strValue = Substring(@strDelimitedString, @Start, @End - @Start)
Set @Start = @End + 1
Set @End = charindex (',',@strDelimitedString, @End+1)

Insert into StudentDetails (StudentId, SubjectName) Values (@NewRowId, @strValue)
End

Commit Tran StudentTransaction

To test this SP:

Exec InsertStudent_Revised 'Vadivel', 29, 'Maths,Science,Chemistry,'

By using a custom split logic we are splitting the values passed into the SP. This approach would be of great use in many cases where it is easy to pass as a comma-seperated value than XML format string.

Comments

DotnetThirsty said…
Only one thing i can say..... Awesome... hats off to u...

Popular posts from this blog

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1

[Non Tech] Want to know the recipe for Omelette :)

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of