March 30, 2010 at 4:36 am
Hi all,
I need your help I have a problem in inserting identity values into a table .The scenario is I have emp table (parent table) and emp_test (child table).the i/p values for emp table will be given as XML values i need to insert this records into emp table as well as into emp_test table forming the parent child relation.the problem is i'm not able to process the indentity values im getting only the last identity values while processing it .Herewith im sending my code for ur usage if anything found to be wrong pls correct me.
code;
--create sample table to test the script
--If table already exists drop and recreate it
if exists (select name from sys.tables where name='emp')
drop table emp
go
create table emp (emp_id int identity(1,1) primary key,f_name varchar(30),l_name varchar(20),designation varchar(40),dob datetime)
--If table already exists drop and recreate it
if exists (select name from sys.tables where name='emp_test')
drop table emp_test
go
create table emp_test (id int identity primary key ,emp_id int)
--If procedure already exists drop and recreate it
if exists (select name from sys.objects where name='prc_xmlinsert')
drop procedure prc_xmlinsert
go
create procedure prc_xmlinsert
(
@i_xml varchar(4000) --I/p variable
)
/*
sample excecution:
------------------
exec prc_xmlinsert @i_xml ='<root>
<Employee>
<FirstName>Kevin</FirstName>
<LastName>Goff</LastName>
<Designation>Software Engg</Designation>
<Dob>2010-03-30 13:23:13.307</Dob>
</Employee>
<Employee>
<FirstName>Steve</FirstName>
<LastName>Waugh</LastName>
<Designation>Sr.Software Engg</Designation>
<Dob>2010-03-30 13:23:13.307</Dob>
</Employee>
<Employee>
<FirstName>Andy</FirstName>
<LastName>Thomson</LastName>
<Designation>Data Analyst</Designation>
<Dob>2010-03-30 13:23:13.307</Dob>
</Employee>
</root>'
*/
as
begin
--declare o/p variable for XML
declare @i int,@l_error_no int
declare @l_identity int
exec sp_xml_preparedocument @i output,@i_xml
BEGIN TRY
BEGIN TRAN
--Read the XML content and insert into table
insert into emp SELECT FirstName,LastName,Designation,Dob FROM OPENXML (@i, '/root/Employee',2)
with(Empid int,FirstName varchar(50),LastName varchar(50),Designation varchar(50),Dob datetime)
select @l_identity=@@identity
insert into emp_test values(@l_identity)
EXEC sp_xml_removedocument @i
COMMIT TRAN
END TRY
BEGIN CATCH
-- insertion failed
SET @l_error_no = 2000
SELECT @l_error_no AS error_no
ROLLBACK TRANSACTION
RETURN
END CATCH
--insertion success
SET @l_error_no = 1000
SELECT @l_error_no AS error_no
end
Thanks In Advance
Chandru
March 30, 2010 at 10:47 am
Hi Chandru,
select @l_identity=@@identity
will only return the last row and not all rows. You'd need to use the OUTPUT clause together with an intermediate table. See the code snippet below.
Please note that I used XQuery instead of OPENXML. It provides more functionality (if needed).
Side note: What is the reason to store the id values in a separate table? Sonds strange...
DECLARE @MyTableVar TABLE( empid INT);
INSERT INTO emp(f_name ,l_name ,designation ,dob)
OUTPUT INSERTED.emp_id
INTO @MyTableVar
SELECT
v.value('FirstName[1]','varchar(30)') AS FirstName,
v.value('LastName[1]','varchar(20)') AS LastName,
v.value('Designation[1]','varchar(40)') AS Designation,
v.value('Dob[1]','datetime') AS Dob
FROM @xml.nodes('root') T(c)
CROSS APPLY
t.c.nodes('Employee') U(v)
INSERT INTO emp_test(emp_id)
SELECT *
FROM @MyTableVar
SELECT *
FROM emp
SELECT *
FROM emp_test
March 31, 2010 at 12:37 am
Hi Imu,
Thanks for the kindly help .the code which you have given is very helpful for me.
Side note: What is the reason to store the id values in a separate table? Sonds strange-->The tables which i have given is just sample its not fully normalised design .Anyways your example helped me alot.
Thanks ,
Chandru
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply