SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best way to insert into two related tables?


Best way to insert into two related tables?

Author
Message
Patrick Hill-489170
Patrick Hill-489170
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8854 Visits: 8490
In one sproc, maybe make use of Scope_identity()?


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
OTF
OTF
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 4128
You can also take a look at using OUTPUT.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211137 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38722 Visits: 14411
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
math martinez
math martinez
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 62
Create store procedure that retrieve data that inserted in violation table and then insert it to the other table. matter of execution sequence..
drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15227 Visits: 11199
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211137 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search