Search This Blog

2010-02-24

Dynamic Bulk Copy using OPENXML

SQL Stored Proc :
USE [WMMM]
GO
/****** Object: StoredProcedure [dbo].[spBulkInsert] Script Date: 02/24/2010 10:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spBulkInsert]
@ITEMS NTEXT,
@TABLENAME VARCHAR(2000),
@COLUMN_NAME VARCHAR(2000),
@COLUMNNAMESWITHDATATYPE VARCHAR(2000),
@DELETEOLDRECORD VARCHAR(1)

AS
DECLARE @INSERT AS NVARCHAR(2000)
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
if @DELETEOLDRECORD='Y'
BEGIN
EXEC('DELETE FROM '+@TABLENAME)
END
DECLARE @HDOC INT
EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT,@ITEMS
SET @INSERT=
'INSERT INTO '+@TABLENAME +'('+@COLUMN_NAME +')
SELECT '+ @COLUMN_NAME +'
FROM OPENXML (@HDOC2, ''/ITEMS/ITEM'',2)
WITH ('+ @COLUMNNAMESWITHDATATYPE +') XMLITEMS'

DECLARE @PARMDEFINITION NVARCHAR(500);
SET @PARMDEFINITION = N'@HDOC2 INT';

EXECUTE SP_EXECUTESQL @INSERT, @PARMDEFINITION,
@HDOC2 = @HDOC;

EXEC SP_XML_REMOVEDOCUMENT @HDOC
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
EXEC dbo.sp_GetErrorInfo
END CATCH
END


Execute The Above Sp Input Parameters :
USE [WMMM]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[spBulkInsert]
@ITEMS = N'<ITEMS><ITEM><PRODUCTID>1</PRODUCTID><NAME>..</NAME><PARENTID>0</PARENTID></ITEM><ITEM><PRODUCTID>2</PRODUCTID><NAME>CHOCO DELIGHT</NAME><PARENTID>1</PARENTID></ITEM><ITEM><PRODUCTID>3</PRODUCTID><NAME>WOW VANILLA</NAME><PARENTID>1</PARENTID></ITEM><ITEM><PRODUCTID>4</PRODUCTID><NAME>CHOCO2</NAME><PARENTID>2</PARENTID></ITEM><ITEM><PRODUCTID>5</PRODUCTID><NAME>CHOCO2_3</NAME><PARENTID>4</PARENTID></ITEM><ITEM><PRODUCTID>6</PRODUCTID><NAME>CHOCO2_3_4</NAME><PARENTID>5</PARENTID></ITEM><ITEM><PRODUCTID>7</PRODUCTID><NAME>CHOCO2_3_4_5</NAME><PARENTID>6</PARENTID></ITEM></ITEMS>',
@TABLENAME = N'TBLTEST',
@COLUMN_NAME = N'PRODUCTID,NAME,PARENTID',
@COLUMNNAMESWITHDATATYPE = N'PRODUCTID BIGINT,NAME VARCHAR(50),PARENTID BIGINT',
@DELETEOLDRECORD = N'N'

SELECT 'Return Value' = @return_value

GO

--@COLUMN_NAME and @COLUMNNAMESWITHDATATYPE must be match with the destination
--table name i.e TBLTEST


Table Schema of tblTest:

USE [WMMM]
GO
/****** Object: Table [dbo].[tblTest] Script Date: 02/24/2010 10:29:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTest](
[ProductID] [bigint] NULL,
[Name] [varchar](50) NULL,
[ParentID] [bigint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF