Hey all, I'm trying to insert a bunch of rows from an xml document into an SQL database. I have a stored procedure that I'm calling from my program after I generate the XML string and pass it into the SP. It runs with no (visible) errors, but when I look at the table, no new rows have been inserted. I'm really stumped, as I'm fairly new to SQL Server and Stored Procedures, and not very experienced with SQL either. Am I doing something wrong here? (SQL below)

Anyway, here's my query. Basically it takes in a string of XML, transforms it into a temporary table (#tmpParms), deletes everything in the current table that is in the temporary table, then inserts everything in the temporary table.

ALTER procedure [dbo].[HOP_ITGTickets_Reinsert] @XMLParms text as

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlParms

SELECT *
into   #tmpParms
FROM   OPENXML (@idoc, '/InvestAdminDataSet/HOP_Tickets_From_Excel',2)
          WITH (ITGNumber int 
	  	,RequestType  varchar(50)
		,RequestStatus varchar(50)
		,CreatedBy varchar(50)
		,ApplicationName varchar(100)
		,LawsonProject varchar(100)
		,PlannedAcceptanceDate varchar(20)
		,PlannedProductionDate varchar(20)
		,WorkgroupCoordinator varchar(50)
		,ALLApprovalsComplete varchar(20)
		,BusinessApprover varchar(50)
		,ITApprover varchar(50)
		,LastUpdated varchar(20)
		,LeadDeveloper varchar(50)
		,SubmitDate varchar(20)
		,RequestName varchar(200) )

EXEC sp_xml_removedocument @idoc
BEGIN TRANSACTION

DELETE FROM ITGTickets
WHERE ITGNumber IN (SELECT ITGNumber FROM #tmpParms)

insert into dbo.ITGTickets (ITGNumber,RequestType, RequestStatus, CreatedBy, ApplicationName, 
LawsonProject, PlannedAcceptanceDate, PlannedProductionDate, WorkgroupCoordinator, ALLApprovalsComplete,
BusinessApprover, ITApprover, LastUpdated, LeadDeveloper, SubmitDate, RequestName)
select *
from #tmpParms
IF @@ERROR <> 0
	BEGIN
	ROLLBACK
	RAISERROR('Something Went Wrong', 16, 1)
	END

COMMIT
Re: Using a SP to insert only new rows in SQL Server 80 80

Can you please try that on SSMS, and see what happens? it may there something wrong from C#.

Re: Using a SP to insert only new rows in SQL Server 80 80

Ah, you were correct. It was a formatting error that was happening before the data ever got to the DB. Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.