Post reply

Best way to insert into two related tables?

  • February 15, 2013 at 6:33 am

    #394194

    Hi,

    My front end has a form that allows the user to input information

    I have the following tables:

    Violations

    Financials

    Checks

    Court dates

    My violations table has a primary key Violation_ID

    the other tables have a foreign key V_ID

    The form where the user inputs the data contains fields for all the tables

    What I'm trying to do is when I do an insert it will populate all V_ID fields with the violation_ID from the violations table but the Violation_ID field is an identity field so I wouldn't have a value to populate the V_ID field in the other tables until after the data that goes into the Violations table is inserted

    What's the best way to accomplish this?

    Thanks in Advance

  • February 15, 2013 at 6:47 am

    #1588290

    In one sproc, maybe make use of Scope_identity()?

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 15, 2013 at 8:19 am

    #1588346

    You can also take a look at using OUTPUT.

  • February 15, 2013 at 3:18 pm

    #1588536

    OTF (2/15/2013)

    You can also take a look at using OUTPUT.

    I've found that to be a wee bit more difficult than you would think unless you have an alternate key to reliably express the new value across the other tables.

    With that thought in mind, do you have a coded example of how to do this?

    --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)

  • February 15, 2013 at 4:13 pm

    #1588557

    How many violations are needing to be submitted at one time from the front-end? Or are you passing them to the database one-at-a-time? I am thinking table-valued parameters may be useful here to pass the data...but that is sort of an aside to the question being asked.

    If it's one violation per call then SCOPE_IDENTITY() might be all you need. If it's more than that OUTPUT could most likely help you. Can you provide the DDL for the tables and sample data that you would send to the database that you would want committed to the four tables and linked accordingly?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 15, 2013 at 8:39 pm

    #1588578

    Create store procedure that retrieve data that inserted in violation table and then insert it to the other table. matter of execution sequence..

  • February 19, 2013 at 2:45 pm

    #1589381

    Jeff Moden (2/15/2013)

    OTF (2/15/2013)

    You can also take a look at using OUTPUT.

    I've found that to be a wee bit more difficult than you would think unless you have an alternate key to reliably express the new value across the other tables.

    With that thought in mind, do you have a coded example of how to do this?

    This used to be true in SQL 2005, but it's no longer true as of SQL 2008. I'll work on writing up an article in the next few days.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 19, 2013 at 4:13 pm

    #1589401

    drew.allen (2/19/2013)

    Jeff Moden (2/15/2013)

    OTF (2/15/2013)

    You can also take a look at using OUTPUT.

    I've found that to be a wee bit more difficult than you would think unless you have an alternate key to reliably express the new value across the other tables.

    With that thought in mind, do you have a coded example of how to do this?

    This used to be true in SQL 2005, but it's no longer true as of SQL 2008. I'll work on writing up an article in the next few days.

    Drew

    Very cool. I'd love to see it, Drew. In fact, if you'd like, I'll be happy to do a technical review on it, if you'd like.

    --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 8 posts - 1 through 7 (of 7 total)

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