0

I wrote the following procedure to do bulk insert using openxml. The table name and column names are dynamic in my case as they will be created from .Net application just before this below insert. The stored procedure is running successfully but not inserting any data into the table.

Please help me

Thanks in advace.

CREATE PROCEDURE [dbo].[BulkUpdate]
@XmlDoc text,
@TableName nvarchar(50),
@ColumnNames nvarchar(4000),
@ColumnDefinition nvarchar(4000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Idoc int;
Declare @Sql nvarchar(4000);
BEGIN TRANSACTION
Begin Try
Exec sp_xml_preparedocument @Idoc output, @XmlDoc;

Set @Sql = 'Insert into '+@TableName+'('+@ColumnNames+')

Select '+@ColumnNames+' From OpenXML(@Idoc,''/NewDataSet/Data'', 2)

With('+@ColumnDefinition+')';

Print @sql;
Exec @Sql;

Exec sp_xml_removedocument @Idoc;
Commit Transaction
End Try
Begin Catch
RollBack Transaction
End Catch

END
gee'K'iran
  • 431
  • 2
  • 7
  • 21
  • Albeit, I have implemented the above functionality by generating sql from .net code itself, I just want to know what is wrong with the above sp. – gee'K'iran Nov 08 '12 at 11:37
  • 1
    can you post the insert statement generated dynamically ? I would suggest to compare your dynamic sql statement from the statements [http://msdn.microsoft.com/en-us/library/aa276847%28v=sql.80%29.aspx] exists here, because it depends what information you want to extract from xml. If select returns something definitely a reflection would appear. – NeverHopeless Nov 10 '12 at 23:36

1 Answers1

0

I observed that SqlClient.BulkCopy as the better performant than the above OpenXML approach. Hence I changed my logic accordingly.

gee'K'iran
  • 431
  • 2
  • 7
  • 21