Insert data within multiple tables in SQL Server 2008

Question

I have 3 different tables in my database as below;

Table 1:
ResourceID |  ResourceTitle|  ResourceCategory

Table 2:
DocumentID|  DocName  |  DocSize

Table 3:
ResourceID  |  DocumentID

Now I want to add some values into the above tables (using a C# .ASP.NET) respectively.

The first table I have one row to be added into the Resource table(Table 1).
The second table I have multiple rows to be added into the document table(Table 2).
The third table needs to keep all the ids from the first two tables as a foreign key in ResourceDocument Table(Table 3).

All of the processes need to be done in one single transaction, so there would be a loop for the second table data in my asp.net c# class.

the problem that I have is finding the right way to handle the transaction to do this job, also I need to make sure while these processes are running it doesn't let the other users to modify these data.

Your help would be appreciated.

what exactly is the problem with right way to handle transaction? If you're using some kind of ORM, there's a big possibility that it uses unit of work pattern and all your database actions will be wrapped to transaction on commit, if not, you can use `TransactionScope` to handle transaction scope manually. — Giedrius, Jun 11 '13 at 06:42
If you using Linq, you can do all your changes and at the end of ever Loop cast the SubmitChanges() method. I'm not sure where the Problem is? http://msdn.microsoft.com/en-us/library/bb655883(v=vs.90).aspx — Smartis has left SO again, Jun 11 '13 at 06:47

score 2 · Answer 1 · answered Jun 11 '13 at 07:24

2

try this

DECLARE @ResourceID int
 DECLARE @DocumentID int
 Insert into Table 1(ResourceTitle,  ResourceCategory)value("values","values")          

 SELECT @ResourceID=SCOPE_IDENTITY()
 Insert into Table 2( DocName , DocSize) value("values","values")
 SELECT @DocumentID =SCOPE_IDENTITY()
 Insert into Table 3( ResourceID  ,  DocumentID) values(@ResourceID ,@DocumentID)

answered Jun 11 '13 at 07:24

Deepak Saralaya

457
4
12

Thanks for your answer, I have written the same query but as I said I need to add multiple rows in the second Table at the same time, So when a user click on a button then a resource including multiple documents save in a database at the same time. The way that you wrote this is for one resource and one document only. – Ali Jun 12 '13 at 01:06

score 1 · Answer 2 · answered Jun 11 '13 at 07:02

1

You can use transaction scope as you can see this link

http://www.dotnetjalps.com/2010/05/using-transactions-with-linq-to-sql.html

answered Jun 11 '13 at 07:02

Tushar Maru

3,347
10
34
53

score 1 · Accepted Answer · edited May 23 '17 at 12:13

You can use table valued parameters in stored procedures.

This is a good LINK how to create & use TVP in stored procedures.

Then you need to pass TVP from your page/form.

You can have a look HERE to get an idea for that.

Example for creating table type :

CREATE TYPE yourDocumentTableType AS TABLE 
(
    docID INT NOT NULL IDENTITY(1, 1),
    docName VARCHAR(50),
    docSize INT 
)
GO

Your stored procedure should be like this

CREATE PROCEDURE yourInsertOperation 
    -- Add the parameters for the stored procedure here
    @Param1 INT, 
    @Param2 VARCHAR(20),
    --......your parameter list goes here
    -- here you would pass the records for your document table
    @YourTableValuedParam dbo.yourDocumentTableType READONLY 
AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- declare temp table type & assign the TVP to it
        DECLARE @TempDocuments dbo.yourDocumentTableType

        INSERT INTO @TempDocuments (docName, docSize)
        SELECT docName, docSize FROM @YourTableValuedParam

        DECLARE @ResourceID BIGINT            

        --Marking the start of a transaction
        BEGIN TRANSACTION

        --Inserting records into resource table
        INSERT INTO resourceTable(column1, column2) VALUES (@param1, @param2)

        --selecting resourceID to be inserted into document table & resource-document table
        SELECT @ResourceID = SCOPE_IDENTITY()

        WHILE EXISTS(SELECT * FROM @TempDocuments)
            BEGIN                        
                DECLARE @DocumentID BIGINT

                --Inserting records into document table from the table valued parameter
                INSERT INTO documentTable(docName, docSize, resourceID)
                SELECT TOP 1 docName, docSize, @ResourceID FROM @TempDocuments

                SELECT @DocumentID = SCOPE_IDENTITY()
                INSERT INTO resouceDocumentTable(resourceID, docID) VALUES (@ResourceID, @DocumentID)

                DELETE TOP (1) FROM @TempDocuments
            END

    --Checking for any error in the whole transaction (all 3 table insert operation)
    IF @@ERROR > 0
        --rollback if there was any error
        ROLLBACK TRANSACTION
    ELSE
        --commit whole transaction (all 3 table insert operation)
        COMMIT TRANSACTION

END
GO

The above strategy is good but the code is not working properly, the following line has error (Must declare the scalar variable) 'SET TempDocuments = YourTableValuedParam ' — Ali, Jun 13 '13 at 03:00
@AliShahrokhi, check now the code is working. Instead of SET statement, it needed to use insert statement, as the variable is of type table. I have tested this & it is working. Sorry for delayed response. — Devraj Gadhavi, Jun 14 '13 at 07:30

Insert data within multiple tables in SQL Server 2008

3 Answers3