Sql Bulk Insert

Sql Bulk Insert -

Ref Links -
http://consultingblogs.emc.com/markharrison/archive/2009/09/15/fastest-way-to-insert-bulk-records-into-sql-server.aspx

http://www.c-sharpcorner.com/blogs/7125/bulk-insert-into-sql-database-using-xml-file.aspx

use master
DECLARE @TESTPB TABLE (Lr_no Varchar (50), Bkg_Stn Varchar(30) , FrmShort Varchar(5))
DECLARE @xml XML;
SELECT @xml = (
 SELECT * FROM OPENROWSET(
 BULK 'D:\newxf.xml',
 SINGLE_BLOB) x)
INSERT INTO @TestPB(LR_no,Bkg_Stn,FrmShort)
SELECT  X.product.value('LR_no[1]', 'Varchar(50)'),
  X.product.value('Bkg_Stn[1]', 'Varchar(30)'),
  X.product.value('FrmShort[1]', 'Varchar(5)')
FROM
@xml.nodes('NewDataSet/LRTest') AS X(product);
INSERT INTO Master.dbo.IExml
Select * From @TestPB

/* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATETIME Not Null , customer_id INT Not Null   CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) );   CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product_id INT Not Null   CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id)   CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) ); Go   /* Create a new procedure using an XML parameter */ CREATE PROCEDURE dbo.insert_orderXML_sp @orderDate DATETIME , @customer_id INT , @orderDetailsXML XML AS BEGIN   SET NOCOUNT ON;   DECLARE @myOrderID INT;   INSERT INTO dbo.orders ( orderDate , customer_id ) VALUES ( @orderDate , @customer_id );   SET @myOrderID = SCOPE_IDENTITY();   INSERT INTO dbo.orderDetails ( order_id , lineItem , product_id ) SELECT @myOrderID , myXML.value('./@lineItem', 'int') , myXML.value('./@product_id', 'int') FROM @orderDetailsXML.nodes('/orderDetail') As nodes(myXML);   SET NOCOUNT OFF;   END GO     /* Call our stored procedure */ EXECUTE dbo.insert_orderXML_sp @orderDate = '2008-01-01' , @customer_id = 101 , @orderDetailsXML = '<orderDetail lineItem="1" product_id="123" /> <orderDetail lineItem="2" product_id="456" /> <orderDetail lineItem="3" product_id="789" /> <orderDetail lineItem="4" product_id="246" /> <orderDetail lineItem="5" product_id="135" />';     /* Check our data */ SELECT * FROM dbo.orders; SELECT * FROM dbo.orderDetails;     /* Clean up our mess */ DROP PROCEDURE insert_orderXML_sp; DROP TABLE dbo.orderDetails; DROP TABLE dbo.orders;