Perform multiple inserts and terminate is errors 2

[robertfah]

I need a way to insert multiple records into 2 tables and terminate the process if any errors occur.

I have Issues and IssueActivities that I will be migrating over to another DB through a program. For every 1 Issue, there are many IssueActivities. The data doesn't match up (meaning ID's are not the same, etc.) so the program takes the Issue info from DB#1 and finds the best match or default values for DB#2. The same can be said for the IssueActivity info but again, there are several records for each Issue. Capturing and translating the data isn't the problem, it's trying to figure out how in a SP I can insert the 1 Issue record into the Issue table and insert ALL of the IssueActivity records into the IssueActivities table and perform a rollback if any of it fails.

Any ideas?

 

[markros]

Well, wrap the whole process in the transaction and rollback in the case of failure.

Here is a sample of how such logic can be written using try-catch:
Inserting into multiple tables

PluralSight Learning Library

 

[robertfah]

Well, wrap the whole process in the transaction and rollback in the case of failure.Here is a sample of how such logic can be written using try-catch:
Inserting into multiple tables

Thanks for that link...some useful stuff in there. I guess my question is how do I construct/design the SP to handle the "insert 1 record into 1 table and many records into another table" process. I know to wrap it all in a transaction but how do I pass multiple rows of data to be inserted to a SP?

CREATE PROCEDURE InsertIssue
  @IssueID int,
  ** 30+ variables omitted.
AS

  --Insert data into Issue table (1 record)
  INSERT INTO Issues (**values omitted**)
  VALUES (@IssueID, **vaiabled omitted**)

  IF(Successful)
    --Insert Multiple Records into IssueActivities table.
    --  How??

 

[markros]

Well, you can use insert select statement, e.g.
insert into table2 (...)
select ...
from ...

But this is all I can tell for now without more info.

PluralSight Learning Library

 

[robertfah]

Ok, I'll try to explain it a little better...

We've got a 3rd party system with the tables Issues and Issue_Activities. We have created our own software program which will replace this 3rd party system. I am in the process of merging all the 3rd party Issues and Issue_Activities to our program. Obviously the ID's and data won't match so I created a conversion tool that will attempt to convert the 3rd party Issues and Issue_Activities into our program.

The program successfully converts all the Issues and Issue_Activites but it's Inserting this new data where I am having trouble. For each Issue, there could be 1 or more Issue_Activies so the data could look like this:

ISSUES:
IssueID,
CustID,
IssueTypeID,
Summary,
Description

1, 5, 3, 'Test summary1', 'Test description1'
2, 5, 3, 'Test summary2', 'Test description2'
3, 5, 3, 'Test summary3', 'Test description3'
4, 5, 3, 'Test summary4', 'Test description4'

ISSUE_ACTIVITIES
IssueActivityID,
IssueID,
DateTime,
ActivityNote

1, 1, '11-14-2011', 'Activity Note For Issue # 1'
2, 1, '11-14-2011', 'Another Activity Note For Issue # 1'
3, 1, '11-14-2011', 'Yet Another Activity Note For Issue # 1'
4, 2, '11-14-2011', 'Activity Note For Issue # 2'
5, 3, '11-14-2011', 'Activity Note For Issue # 3'
6, 2, '11-14-2011', 'Another Activity Note For Issue # 3'
7, 4, '11-14-2011', 'Activity Note For Issue # 4'

So when I go to actually INSERT the data into our DB, I need to be able to insert all issue records and related issue activity records, but if there's a failure with any insert (either inserting an Issue or Issue_Activity) then insert some data into a test table (probably just IssueID) and rollback that transaction. This would ensure that if an issue got inserted and there was a problem with one of the related issue_activities, then the issue wouldn't get inserted.

So how can this be achieved in a SP? Essentially the SP would have to have the Issue information passed to it and the Issue_Activity information stored in some form of array or something so that it could loop through the 'many' Issue_Activities that an Issue could have.

 

[markros]

What is your current SP?

PluralSight Learning Library

 

[robertfah]

What is your current SP?

Here's what I've got so far:

CREATE PROCEDURE [dbo].[ASC_SCS_InsertMergeData]
	@IssueID int,
	@CustomerID int,
	@IssueTypeID int,
	@IssueSummary varchar(255),
	@IssueDescription text

AS

	INSERT INTO Issues
	VALUES(@IssueID, @CustomerID, @IssueTypeID, @IssueSummary, @IssueDescription)

Don't know how to get the Issue_Activies (converted data) to this SP. It's not stored in a table or anything, just an object that the conversion program creates. Sorry, I meant to put that in my last reply.

 

[markros]

What is your SQL Server version and what is the application you're using?

Assuming that you're using SQL Server 2008, you can pass the Issue_Activities records as TVP (table valued parameter).

In prior versions you may want to pass it as XML and then you will need to work with XML data to convert it back to relational data.

PluralSight Learning Library

 

[robertfah]

We are using SQL 2000....We are on 2008 in our Staging environment but haven't converted in production yet. Hopefully in the next couple of months we will.

Looks like XML then....ugh...

 

[gmmastros]

What language is your app built in?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

What language is your app built in?

c#, using Visual Studio 2010

 

[gmmastros]

If you use a stored procedure, the best method is to pass XML to a parameter. This can get a little messy but works reasonably well, especially if the XML isn't too large.

Another method would be to construct your query in the app with the data. Hard to explain, but.... something like this (using VB syntax):

Dim SQL as String

SQL = "Create Table #TempIssueActivities(IssueActivityID INT, IssueId Int,[DateTime] DateTime,ActivityNote varchar(Whatever))"

SQL = SQL & "Insert Into #TempIssueActivities Values(1,2,'20111114','blah')"

SQL = SQL & "Insert Into #TempIssueActivities Values(1,3,'20111114','blah')"

SQL = SQL & "Insert Into #TempIssueActivities Values(1,4,'20111114','blah')"

SQL = SQL & "Insert Into #TempIssueActivities Values(1,5,'20111114','blah')"

SQL = SQL & "Exec MyStoredProcedure(Param1, Param2)"

Inside the stored procedure, the temp table will exist with all the data in it. The trick is, build the temp table, fill it with data, and execute the stored procedure all within the same call to the database. This will keep everything in the same session and the temp table you build outside the stored procedure will be available inside it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

I think I see where you are going with this....I will give it a shot and let you know how it turns out.

Thanks for the replies markros & gmmastros.

 

[robertfah]

Just wanted to share my solution with everyone, in case someone else is trying to do the same thing. I went the XML route because it was fairly easy (not that GMM's was difficult) and I had some code lying around that helped me out. In the end, here's the SP I came up with:

ALTER   PROCEDURE [dbo].[ASC_SCS_InsertHDAIssueAndActivities]
	@IssueID int,
	@DateCreated datetime, 
	@ReceivedDate datetime, 
	@DueDate datetime = NULL, 
	@ResolvedDate datetime = NULL, 
	@ResolvedByUserID int = NULL, 
	@AssignedGroupID int = NULL, 
	@UpdatedByUserID int = NULL,
	@CreatedByUserID int, 
	@AssignedUserID int, 
	@IssueStatusID int, 
	@IssueSubStatusID int, 
	@PriorityID int, 
	@IssueCategoryID int, 
	@ConfigurationID int, 
	@LocationID int, 
	@RoomID int, 
	@POCID int, 
	@IssueTypeID int, 
	@VersionID int, 
	@ProductID int, 
	@SupportVisitTypeID int = NULL, 
	@CallbackNumber varchar(50) = NULL, 
	@AlternateEmail varchar(200) = NULL,  
	@IssueSummary varchar(255), 
	@IssueDescription text, 
	@LastUpdatedDate datetime = NULL, 
	@DRNumber varchar(50) = NULL, 
	@DRLink varchar(500) = NULL,	
	@ECRNumber varchar(50) = NULL,  
	@ECRLink varchar(500) = NULL, 	
	@IssueResolution text = NULL, 
	@EquipmentReplacedID int = NULL, 
	@EquipmentReplacedQuantity int = NULL, 
	@EquipmentDescription varchar(500) = NULL, 
	@ProjectedSoftwareVersionID int = NULL, 
	@ReleasedSoftwareVersionNumber varchar(25) = NULL,
	@OriginID int,
	@xmlActivities ntext

 AS
	DECLARE @FileHandle int
	
	BEGIN TRANSACTION
		--Attempt to insert Issue.
		INSERT INTO I(IssueID, DateCreated, ReceivedDate, DueDate,	ResolvedDate, ResolvedByUserID,
			AssignedGroupID, UpdatedByUserID, CreatedByUserID, AssignedUserID, IssueStatusID, IssueSubStatusID, PriorityID,
			IssueCategoryID, ConfigurationID, LocationID, RoomID, POCID, IssueTypeID, VersionID, ProductID,	SupportVisitTypeID,
			CallbackNumber,	AlternateEmail,	IssueSummary, IssueDescription,	LastUpdatedDate, DRNumber, DRLink, ECRNumber,
			ECRLink, IssueResolution, EquipmentReplacedID, EquipmentReplacedQuantity, EquipmentDescription,	ProjectedSoftwareVersionID,
			ReleasedSoftwareVersionNumber, OriginID)
		VALUES (@IssueID, @DateCreated, @ReceivedDate, @DueDate, @ResolvedDate, @ResolvedByUserID,
			@AssignedGroupID, @UpdatedByUserID, @CreatedByUserID, @AssignedUserID, @IssueStatusID, @IssueSubStatusID, @PriorityID,
			@IssueCategoryID, @ConfigurationID, @LocationID, @RoomID, @POCID, @IssueTypeID, @VersionID,	@ProductID,	@SupportVisitTypeID,
			@CallbackNumber, @AlternateEmail, @IssueSummary, @IssueDescription, @LastUpdatedDate, @DRNumber, @DRLink, @ECRNumber,
			@ECRLink, @IssueResolution, @EquipmentReplacedID, @EquipmentReplacedQuantity, @EquipmentDescription, @ProjectedSoftwareVersionID,
			@ReleasedSoftwareVersionNumber, @OriginID)		
	
		--Rollback on errors.
		IF @@ERROR != 0
			BEGIN
				ROLLBACK TRANSACTION
				RETURN
			END
		
	  --Attempt to insert Issue Activity.
	  --Convert the xml data into a recordset.
	  EXEC dbo.sp_xml_preparedocument @FileHandle output, @xmlActivities
	  
	  INSERT INTO IA (IssueActivityTypeID, IssueID, CreatedByUserID, ActivityDate, TimeSpent, Notes, Notify)
	  SELECT IssueActivityTypeID, IssueID, CreatedByUserID, ActivityDate, TimeSpent, Notes, Notify
	  FROM OPENXML(@FileHandle, 'Activities/Activity')
	  WITH (IssueActivityTypeID int 'IssueActivityTypeID', IssueID int 'IssueID', CreatedByUserID int 'CreatedByUserID',
	  ActivityDate datetime 'ActivityDate', TimeSpent int 'TimeSpent', Notes text 'Notes', Notify bit 'Notify')
		  
		--Rollback on errors.
		IF @@ERROR != 0
			BEGIN
				ROLLBACK TRANSACTION
				RETURN
			END
		ELSE
	COMMIT TRANSACTION

The front-end uses some simple code to create XML and save it to a memory stream and then I pass that to the SP as one big string. I do have to remove the xml Declaration (in c#) as SQL doesn't like the header info.

Thanks again for everyone's help!