Bulk insert in database stored procedure RSS

• • Reply
  

  kook23

  Member

  28 Points

  40 Posts

  Bulk insert in database stored procedure

  Jan 04, 2013 05:52 PM|LINK

  Hi All,

  I want to insert data into sql server table .

  My table is say Product(Id as P.K int ,Name as varchar)

  I want to write a stored procedure which will insert/update(if inserted any) multiple records.It may take xml as input parameter to SP.

  Need help.

  • Moved by XIII on Jan 04, 2013 05:54 PM
• • Reply
  

  santosh.jagd...

  Star

  7625 Points

  1454 Posts

  Re: Bulk insert in database stored procedure

  Jan 05, 2013 08:24 AM|LINK

  here is sample code

  CREATE PROC AbsorbentOrderBulkInsertXML @items nText
  
  AS
  DECLARE @hDoc int
  exec sp_xml_preparedocument @hDoc OUTPUT, @items
  
  Insert Into absorbentorder
  SELECT dpc, absorbentcode, quantity
  FROM OPENXML (@hDoc, '/absorbentitems',1)
  WITH (dpc char(15), absorbentcode char(15), quantity char(15)) XMLItems

  more help http://social.msdn.microsoft.com/Forums/en/transactsql/thread/ca8ce28f-294c-4b5c-bbaf-5ee32c80f394

  Santosh J
  MCP
  
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Bulk insert in database stored procedure

  Jan 05, 2013 09:57 AM|LINK

  kook23

  kook23

  Hello，

  I think you can just check whether your id exists or not, if yes, do updating；otherwises do inserting.

  for more you can see this：

  CREATE PROC AbsorbentOrderBulkInsertXML
  @id int
  AS
  DECLARE @hDoc int,@items nText
  select @hDoc = count(*) from xxxTable
  
  if(@hDoc>0) then
     update ……
  else
     insert ……
  end if
  
  exec sp_xml_preparedocument @hDoc OUTPUT, @items
   
  Insert Into absorbentorder
  SELECT dpc, absorbentcode, quantity
  FROM OPENXML (@hDoc, '/absorbentitems',1)
  WITH (dpc char(15), absorbentcode char(15), quantity char(15)) XMLItems

• • Reply
  

  kook23

  Member

  28 Points

  40 Posts

  Re: Bulk insert in database stored procedure

  Jan 07, 2013 04:56 PM|LINK

  What I have done is :

  Create Procedure addtodb @exml xml
  as
  begin
  Insert into [student].[dbo].[User](UserId,UserName)
  Select p.value('@UserId','integer')as Id,p.value('@UserName','varchar(50)')as Name
  from @exml.nodes('/employeeData/employee') as abc(p)
  end
  
  Exec addtodb @exml = '<employeeData>
  
                    <employee UserId="6" UserName="Rsu"/>
  
                    <employee UserId="7" UserName="PSU"/>
                    
                    <employee UserId="8" UserName="Nsu"/>
                    <employee UserId="8" UserName="Ksu"/>
                    <employee UserId="8" UserName="Dsu"/>
                 </employeeData>'

  thanks for help.
  
  

  • Edited by kook23 on Jan 07, 2013 04:57 PM
  • Edited by decker dong - msft on Jan 10, 2013 02:11 AM
  • Marked as answer by decker dong - msft on Jan 10, 2013 02:11 AM