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

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

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