Best way to insert into two related tables?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Best way to insert into two related tables?

Best way to insert into two related tables?

Rate Topic

Display Mode

Topic Options

Author

Message

Patrick Hill-489170

Posted Friday, February 15, 2013 6:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 6:45 AM
Points: 7, Visits: 29

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

Post #1420502

Cadavre

Posted Friday, February 15, 2013 6:47 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

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

Not a DBA, just trying to learn

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/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1420507

OTF

Posted Friday, February 15, 2013 8:19 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:17 AM
Points: 256, Visits: 3,721

You can also take a look at using OUTPUT.

Post #1420577

Jeff Moden

Posted Friday, February 15, 2013 3:18 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1420801

opc.three

Posted Friday, February 15, 2013 4:13 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 6,718, Visits: 11,753

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1420825

math martinez

Posted Friday, February 15, 2013 8:39 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:40 PM
Points: 14, Visits: 49

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

Post #1420853

drew.allen

Posted Tuesday, February 19, 2013 2:45 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

Jeff Moden (2/15/2013)

OTF (2/15/2013)

You can also take a look at using OUTPUT.

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

Post #1421861

Jeff Moden

Posted Tuesday, February 19, 2013 4:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

drew.allen (2/19/2013)

Jeff Moden (2/15/2013)

OTF (2/15/2013)

You can also take a look at using OUTPUT.

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

Post #1421885

« Prev Topic | Next Topic »

Permissions