locked
2 transactions and rollback RRS feed

  • Question

  • User2055357364 posted

    Hi,

    I have a form that inputs a ton of data.

    2 tables need to be manipulated(INSERTS). (One to many relationship).

    One set of data is represented by one row. (the 1 in the 1 to Many - [PK = "Id"])

    The other set of data is a list of items (the many in the 1 to Many - [PK = "Id"+"Id2"])

    Can this be performed in one transaction(A single sproc)?

    If not, and I need to run two separate sprocs, is there a method to rollback the first sproc if the second fails?

    Uncharted waters for me...

    MzPippz

    Wednesday, June 15, 2016 7:10 PM

Answers

  • User1559292362 posted

    Hi Ms.Longstocking,

    Can this be done in the same query? As in, when I include the parameters from my web app (or C# win app), can I include all of the variables that I need in one, all-encompassing query? Will an error be thrown because some of the variables represent tabular data and some do not?

    According to your description, it seems that you want to do an One-to-Many Inserts via store procedure, we could use Table-Valued Parameters to achieve it. like this:

    #Create Table

    CREATE TABLE [dbo].[Table1]
    (
    	[FieldA] INT NOT NULL PRIMARY KEY, 
        [FieldB] VARCHAR(50) NULL, 
        [FieldC] VARCHAR(50) NULL, 
        [FieldD] VARCHAR(50) NULL
    )
    
    CREATE TABLE [dbo].[Table2]
    (
    	[FieldA] INT NOT NULL, 
        [FieldG] INT NOT NULL, 
        [FieldH] VARCHAR(50) NULL, 
        [FieldM] VARCHAR(50) NULL, 
        [FieldP] VARCHAR(50) NULL, 
        CONSTRAINT [PK_Table2] PRIMARY KEY ([FieldA], [FieldG]) 
    )
    

    #Create Type

    /* Create our new table types */
    CREATE TYPE dbo.orderTable1 AS TABLE 
    ( 
          FieldA     int
        , FieldB   varchar(50)
    	, FieldC   varchar(50)
    	, FieldD   varchar(50)
    );
    GO
     
    CREATE TYPE dbo.orderTable2 AS TABLE 
    ( 
          FieldA      int
        , FieldG    int
    	, FieldH    varchar(50)
    	, FieldM    varchar(50)
    	, FieldP    varchar(50)
    );
    GO

    #Create Store procedure

    CREATE PROCEDURE [dbo].[onetomanyinsertwithtvp]
    	@myOrderTable1 orderTable1 READONLY
        , @myOrderTable2 orderTable2 READONLY
    
    AS
    	BEGIN TRANSACTION
    
    	begin try
    
    	SET NOCOUNT ON;
     
     
        INSERT INTO dbo.Table1
        SELECT FieldA
            , FieldB
    		, FieldC
    		, FieldD
        FROM @myOrderTable1;
     
     
        INSERT INTO dbo.Table2
        SELECT FieldA
            , FieldG
            , FieldH
    		, FieldM
    		, FieldP
        FROM @myOrderTable2;
     
        SET NOCOUNT OFF;
    
    
    	COMMIT TRANSACTION
    
    	end try
    
    	begin catch
    	raiserror('Message here', 16, 1)
    	rollback transaction
    	end catch
    RETURN 0
    

    #Usage

    /* Call our new proc! */
    DECLARE @myTable1 AS orderTable1
        , @myTable2 As orderTable2;
     
    INSERT INTO @myTable1
    (FieldA, FieldB, FieldC, FieldD)
    SELECT 1, 'a','a','a';
     
    INSERT INTO @myTable2
    (FieldA,FieldG,FieldH,FieldM,FieldP)
    SELECT 1, 1,'a','a','a' UNION ALL
    SELECT 1, 2,'a','a','a' UNION ALL
    SELECT 1, 3,'a','a','a' 
     
    EXECUTE dbo.onetomanyinsertwithtvp 
          @myTable1
        , @myTable2;
     
     
    /* Check our data */
    SELECT * FROM dbo.Table1;
    SELECT * FROM dbo.Table2;

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 16, 2016 9:37 AM

All replies

  • User765422875 posted

    In case of error, using SET XACT_ABORT ON a rollback will issued automatically.

    SET XACT_ABORT ON
    
    BEGIN TRANSACTION
    
    INSERT INTO Table1 (id) VALUES (1)
    INSERT INTO Table2 (id) VALUES (1)
    
    COMMIT TRANSACTION

    Or you can use a Try Catch and rollback yourself.

    BEGIN TRANSACTION
    
    begin try
    
    INSERT INTO Table1 (id) VALUES (1)
    INSERT INTO Table2 (id) VALUES (1)
    
    COMMIT TRANSACTION
    
    end try
    
    begin catch
    raiserror('Message here', 16, 1)
    rollback transaction
    end catch

    EDIT

    Yes you can pass in all parameters to the same stored procedure and you can execute the inserts in the same transaction as long as the sequencing is correct. If you are passing tabular data for some of your parameters, then you should consider using Table-Value Parameters.

    I didn't realize you were using c# / ADO.NET. As another option - you can wrap your sql call using the ADO.NET transaction support.

    Wednesday, June 15, 2016 7:42 PM
  • User2055357364 posted

    This is good. My followup:

    Table 1 = insert just one row.

    FieldA[PK], FieldB, FieldC, FieldD

    Table 2 inserts many rows.

    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR
    FieldA[PK], FieldG[PK], FieldH, FieldM, FieldP, FieldR

    Can this be done in the same query? As in, when I include the parameters from my web app (or C# win app), can I include all of the variables that I need in one, all-encompassing query? Will an error be thrown because some of the variables represent tabular data and some do not?

    //Variables Declared to address both inserts. One of one records. The other inserts multiple records
    
    @FieldA
    @FieldB
    @FieldC
    @FieldD
    
    
    @FieldG
    @FieldH 
    @FieldM
    @FieldP
    @FieldR

    I can include some C# code to help in clarifying my question if I'm not being concise enough. I wasn't inclined to do so because this isn't a C# forum... Let me know if I should.

    Thanks,

    MzPippz

    Wednesday, June 15, 2016 8:05 PM
  • User765422875 posted

    I edited my previous answer.

    Wednesday, June 15, 2016 9:44 PM
  • User1559292362 posted

    Hi Ms.Longstocking,

    Can this be done in the same query? As in, when I include the parameters from my web app (or C# win app), can I include all of the variables that I need in one, all-encompassing query? Will an error be thrown because some of the variables represent tabular data and some do not?

    According to your description, it seems that you want to do an One-to-Many Inserts via store procedure, we could use Table-Valued Parameters to achieve it. like this:

    #Create Table

    CREATE TABLE [dbo].[Table1]
    (
    	[FieldA] INT NOT NULL PRIMARY KEY, 
        [FieldB] VARCHAR(50) NULL, 
        [FieldC] VARCHAR(50) NULL, 
        [FieldD] VARCHAR(50) NULL
    )
    
    CREATE TABLE [dbo].[Table2]
    (
    	[FieldA] INT NOT NULL, 
        [FieldG] INT NOT NULL, 
        [FieldH] VARCHAR(50) NULL, 
        [FieldM] VARCHAR(50) NULL, 
        [FieldP] VARCHAR(50) NULL, 
        CONSTRAINT [PK_Table2] PRIMARY KEY ([FieldA], [FieldG]) 
    )
    

    #Create Type

    /* Create our new table types */
    CREATE TYPE dbo.orderTable1 AS TABLE 
    ( 
          FieldA     int
        , FieldB   varchar(50)
    	, FieldC   varchar(50)
    	, FieldD   varchar(50)
    );
    GO
     
    CREATE TYPE dbo.orderTable2 AS TABLE 
    ( 
          FieldA      int
        , FieldG    int
    	, FieldH    varchar(50)
    	, FieldM    varchar(50)
    	, FieldP    varchar(50)
    );
    GO

    #Create Store procedure

    CREATE PROCEDURE [dbo].[onetomanyinsertwithtvp]
    	@myOrderTable1 orderTable1 READONLY
        , @myOrderTable2 orderTable2 READONLY
    
    AS
    	BEGIN TRANSACTION
    
    	begin try
    
    	SET NOCOUNT ON;
     
     
        INSERT INTO dbo.Table1
        SELECT FieldA
            , FieldB
    		, FieldC
    		, FieldD
        FROM @myOrderTable1;
     
     
        INSERT INTO dbo.Table2
        SELECT FieldA
            , FieldG
            , FieldH
    		, FieldM
    		, FieldP
        FROM @myOrderTable2;
     
        SET NOCOUNT OFF;
    
    
    	COMMIT TRANSACTION
    
    	end try
    
    	begin catch
    	raiserror('Message here', 16, 1)
    	rollback transaction
    	end catch
    RETURN 0
    

    #Usage

    /* Call our new proc! */
    DECLARE @myTable1 AS orderTable1
        , @myTable2 As orderTable2;
     
    INSERT INTO @myTable1
    (FieldA, FieldB, FieldC, FieldD)
    SELECT 1, 'a','a','a';
     
    INSERT INTO @myTable2
    (FieldA,FieldG,FieldH,FieldM,FieldP)
    SELECT 1, 1,'a','a','a' UNION ALL
    SELECT 1, 2,'a','a','a' UNION ALL
    SELECT 1, 3,'a','a','a' 
     
    EXECUTE dbo.onetomanyinsertwithtvp 
          @myTable1
        , @myTable2;
     
     
    /* Check our data */
    SELECT * FROM dbo.Table1;
    SELECT * FROM dbo.Table2;

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 16, 2016 9:37 AM
  • User2055357364 posted

    Thanks,

    Let me test this out.

    MzPippz

    Thursday, June 16, 2016 11:09 AM
  • User1559292362 posted

    Hi Ms.Longstocking,

    Let me test this out.

    Have you try the code? does it resolve your issue, If so, could you please make it as answer if the reply is helpful to you. If not, if you encounter any problems about the issue, please feel free let us know.

    Best regards,

    Cole Wu

    Monday, June 27, 2016 2:55 AM