February 15, 2013 at 6:33 am
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
In one sproc, maybe make use of Scope_identity()?
February 15, 2013 at 8:19 am
You can also take a look at using OUTPUT.
February 15, 2013 at 3:18 pm
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
February 15, 2013 at 4:13 pm
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
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
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
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply