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.
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