Post reply

Insert one row in one table and multiple rows on a second table inside transaction

  • September 22, 2007 at 7:48 pm

    #118283

    Scenario:

    I have a requirement that needs to insert data from excel spreadsheet  in two sql Server tables (one control table with file information) and (other data table with all related rows referent to the row in the first table.

    How can I best do that?

    Is it possible to do it using one stored procedure with transaction?

    Any detailed ideas would be greatly appreciated.

    Thanks

    Carlos

  • September 23, 2007 at 11:23 am

    #735497

    the more details you can provide, the better the answer.

    here's an example, but I'm not sure how helpful this will be:

    Create Procedure PR_Sample(@somedata varchar(30),@moredata varchar(30) )

    As

    Begin

    SET XACT_ABORT ON

    BEGIN TRAN   --xact abort says if anything in this group fails, rollback everything.

      Declare @id int  -- a variable to catch the identity of the row inserted by the proc

      Insert into SomeTable(SomeData,MoreData)

        Values(@somedata,@moredata) --assumes an Identity column exists int he table

      SET @id =Scope_Identity() --scope of your current session/connection...better than @@IDENTITY

      Insert into OtherTable(SomeTableID,SomeData)

        Values (@id,@somedata) --Assuming this table has a foreign key relationship to the first table

      Insert into YetAnotherTable(SomeTableID,SomeData)

        Values (@id,@somedata) --Assuming this table has a foreign key relationship to the first table  Insert into TheFinalTable(SomeTableID,SomeData)

        Values (@id,@somedata) --Assuming this table has a foreign key relationship to the first table

    COMMIT TRAN

    End

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 23, 2007 at 12:35 pm

    #735500

    Lowell,

    Thanks for your reply.

    My problem is the following the first insert in the first table will have just one row for control information. (i.e. the stored procedure will be called once).

    The second insert will insert many rows corresponding to the rows in the excel file. (threrefore the second stored procedure will have to be called many times).

    I believe that the other way to do it, is to make the transaction outside of the stored procedures, i.e. at the .NET code level, using SQLTransaction, but I am not to familiar with these kind of implementation.

    Thanks again.

  • September 24, 2007 at 4:56 am

    #735561

    "The second insert will insert many rows corresponding to the rows in the excel file. (threrefore the second stored procedure will have to be called many times)."

    Why? You can call procedure once and insert any number of rows. Calling the procedure for each row separately is one of the best ways to produce slow-running code with big potential performance problems.

  • September 24, 2007 at 5:25 am

    #735567

    it sounds like you are entering some master...detail information into a pair of tables;

    As Vladan identified, you should be doing this as a couple of set based operations, instead of Row-By-Agonizing-Row (RBAR).

    RBAR is a method where programmers handle data when being palced into a text file; It's easy to get lockedinto this kind of logic, because it's what you know.

    you should be able to insert into both tables, with just two commands, all the data from an excel sheet. below I'm assuming you are using an excel sheet as a linked server:

    insert into table1 (col1,col2)

      SELECT col1,col2. FROM XLTEST_DMO...Sheet1$

    insert into table2(sample1ID,col1,col2

      select sample1.sampleid,col3,col4 from XLTEST_DMO...Sheet1$

      inner join sample1 on XLTEST_DMO...Sheet1$.col1=sample1.col1 and   XLTEST_DMO...Sheet1$.col2=sample1.col2

    the above is an example of how I might insert some data that was unique based on two columns int he excel spreadsheet into a table with an identity column.

    if you posed the structure of the excel, as well as the structure of the two tables, we could offer better suggestions.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 24, 2007 at 9:38 pm

    #735859

    Carlos,

    There are a number of ways to accomplish this. The implementation will depend on the requirements.

    Options:

    1. Create a DTS/SSIS package to extract, transform, and load the data into staging tables.

    2. Create a .NET app to read from the Excel file, and then use Bulk-Insert to load the data into staging tables.

    Note:

    1. Perform any validation against the data in the staging tables.

    2. Create a stored procedure to copy the data from the staging tables to the actual tables. (You can wrap the T-SQL statements inside a transaction if applicable.)

    3. Again as Lowell has stated, a more detailed explanation of the requirements will help us answer and/or simplify your question.

      • Will the implementation be user-interactive?

      • If so, how do the user(s) plan to use it?

      • Are there any applications involved?

      • Is the request a one-time deal?

       

      Regards,

      Wameng Vang

      MCTS

    • September 25, 2007 at 12:17 am

      #735895

      If you treat Excel like any other application, and you should, you'll soon realize that the best way to do this is to write a little VBS to push the data into the database... like any other app... 😉

      --Jeff Moden

      RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
      First step towards the paradigm shift of writing Set Based code:
      ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

      Change is inevitable... Change for the better is not.

      Helpful Links:
      How to post code problems
      How to Post Performance Problems
      Create a Tally Function (fnTally)

    Viewing 7 posts - 1 through 6 (of 6 total)

    You must be logged in to reply to this topic. Login to reply