Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    11

    Unanswered: Alternative to using a cursor for this situation

    Still new to sql development, but learning a lot fast.

    This scenario would be solved with entity framework, but that is not an option.

    Need to insert data into two tables. Below describes the situation.

    The Main table has an identity key and the sub table needs that key as well when data related to the records tied to the key get inserted.

    The source info is coming from a few other tables. I can't make changes to the Main or Sub table structure.

    I am trying to avoid using a cursor, but I don't see any alternatives.

    I tried using output inserted.main_id into #temp1 - which works, but I can't find a way to put the Sub_ID into that temp table as well. The Sub_ID is coming from the source.

    They way I have it working is inserting the data into the Main table then by matching up a hash in the source against the Main table I can then insert the Main_ID into the Sub table with the corresponding Sub_ID.

    Example

    TABLE MAIN
    Main_ID PK, bigint,not null - identity
    FirstName varchar(15) not null
    LastName varchar(15) not null
    Misc1 varchar(15)null
    Misc2 varchar(15) null
    Misc3 varchar(15) null

    TABLE SUB
    Main_ID PK,FK,bigint, not null
    Sub_ID int null
    Misc4 varchar(15)
    Misc5 varchar(15)
    Misc6 varchar(15)

    Is there a way to capture the Sub_ID along with "output inserted.main_id" into a temp table?

    Right now what I am doing is matching a hash from the source and the records inserted into the Main table to get the Main_ID that will be inserted into the Sub table.

    If I could capture the Sub_ID into a temp table when the Main_ID is generated in the Main table the process would be a lot faster. I wanted to avoid using a cursor, but I think that is my best option right now.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Assuming that Source has the Main_ID and Sub_ID values, you could try constructing your after insert trigger on the main table as:
    Code:
    INSERT INTO tblSub
       (Main_ID
    ,   Sub_ID
    ,   Misc4
    ,   Misc5
    ,   Misc6
       )
    SELECT
        i.Main_ID
    ,   s.Sub_ID
    ,   i.Misc4
    ,   i.Misc5
    ,   i.Misc6
    FROM
       inserted i
    INNER JOIN
       tblSource s
    ON
       i.Main_ID = s.SourceID
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2012
    Posts
    11
    The Main_ID is not coming from the source. It's generated after data is inserted into the main table. After that happens data relating to the main table goes into the sub table. The Main_ID that was generated needs to go into that table as well. The Sub_ID is coming from the source.

    Going to see if I can work with a CTE and make this happen.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Hmm.
    You could try writing the trigger so that tblMain joins to Inserted on all fields bar the key (hoping that you don't have duplicates), and using the results of that to insert data into tblSub?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jun 2012
    Posts
    11
    Quote Originally Posted by weejas View Post
    Hmm.
    You could try writing the trigger so that tblMain joins to Inserted on all fields bar the key (hoping that you don't have duplicates), and using the results of that to insert data into tblSub?
    I was going to do that, but then I found out that MAIN table gets inserts that will not go into the Sub table.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Huh. This is getting less and less straight-forward with each post!
    Is there any means of identifying which records for tblMain that have no subs? If so, that could be folded into the trigger's WHERE clause.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2012
    Posts
    11
    Quote Originally Posted by weejas View Post
    Huh. This is getting less and less straight-forward with each post!
    Is there any means of identifying which records for tblMain that have no subs? If so, that could be folded into the trigger's WHERE clause.
    The main table has two other tables derived from it. They were created with entity framework. So that is why the identity field is in the main and that is why I have to populate the main table first. I match up on a hash to get the identity then insert data accordingly.

    If they would put allow me to have a sub_id in the main then this would be simple. However they are not allowing that for a few reasons.

    Basically the source has

    First Name, Last Name and few other fields that will go into the main. The in the sub table a few more records related to it and a sub_id that relates it back to a different database. Joins are used on the sub_id to relate back to the source for queries.

    I just need a way to capture the main_id into a temp table along with the correlating sub_id that will go into the sub table with the other related fields that was inserted into the main table.

    Been doing some reading and it appears that I should be able to do this with a CTE and/or temp table. Should be fun. I like learning and so I enjoy figuring out a way to make this happen without using a hash.

    I will definitely report back as to how I go it done in order to help others in the future who need something similar.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •