March 10, 2009 at 7:03 pm
Ok, I know this is a DB forum site, but I have a question as to what is the best way and industry standard to get data INSERT'd into multiple tables where Parent tables must be inserted first in order to obtain @@IDENTITY to populate foreign keys in Child tables when inserting records to a RDBMS
I am developing ASP.NET web applications to populate DBs.
The approach I have taken thus far is to use a wizard approach. Fill out a web form on one page populate a parent table and return a key for the next page in the wizard then so on and so on.
My flaws here are 1, what if the user abandons the application in the 3rd or 4th screen and then all the data is orphan’d.
So I am leaning on the DB community to help me design these application to ensure I contain referential integrity and solid data in the database.
Brain storming thoughts:
Is it best to keep all the data in a dataset in memory then when I am ready to commit the data I do it in one store proc insert?
Please provide your thoughts.
March 11, 2009 at 5:47 am
There's no one right answer to this question, but by and large, if you have a general set of data that has to be filled out, then yeah, I'd say you gather it together and pass it in as a single transaction.
As far as the parent/child issue, you can use the OUTPUT clause that comes with SQL Server 2005 to get around this. Basically you insert X number of rows into the parent table and use the OUTPUT clause to capture the generated ID's, which you can then pass with the natural key values to the child table inserts. It's a very clean, set-based method, that will ensure data integrity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2009 at 8:04 am
Grant thanks for the reply can you provide me a simple sample code...
Great quote.
March 11, 2009 at 8:11 am
moojjoo (3/11/2009)
Grant thanks for the reply can you provide me a simple sample code...Great quote.
This is straight out of the books online:
USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2009 at 1:48 pm
Moojjoo: "I know this is a DB forum site, but I have a question "
I really fail to see why you should be concerned with posting this here. Master-Detail relationships such as the one you mention (Parent-Child) IS a major consideration in a DataBase and is entirely appropriate here.
This is from someone who is still stuck in SS2K.
DECLARE @ls_OwnerID varchar(38)
SET @ls_OwnerID = NEWID()
CREATE TABLE Student
(
pk int NOT NULL PRIMARY KEY IDENTITY,
Student_ID varchar(6),
Student_Name varchar(30),
Student_FirstName varchar(30)
)
arrgh!!! where does that smiley come from ?
I used to do something like
INSERT INTO Student (Student_ID, Student_Name, Student_FirstName, OwnerID)
SELECT '0001', 'Ford', 'Harrisson', @ls_OwnerID UNION
SELECT 'A456', 'Baldwin', 'Alec', @ls_OwnerID
...
And then retrieve the primary keys created by doing
SELECT * FROM Student WHERE OwnerID = @ls_OwnerID
And finally
INSERT INTO Student_Grade (fk_Student, fk_Course_ID, Grade, Exam_Date)
SELECT ST.pk, 123, GR.Grade, GR.Exam_Date
FROM Student ST
INNER JOIN Exam GR ON GR.fr_Student = ST.pk
WHERE ST.OwnerID = @ls_OwnerID
Maybe I am stuck in my old ways, but this seems simpler to me than creating a temp table...
March 14, 2009 at 6:47 am
The key question that I have still is what is the industry standard. An idea I am also thinking about is does it really matter leaving orpaned data in the system? (Child tables not complete that should be?) as long as the application is smart enough to know where an end user left off filling out the data in the appliation and also do not provide any reporting on final reports of that data as well.
Also, let the end users know that if the do not complete the entire entry process (were referring to a lot of data being captured in numerous web pages) that there data will be removed if not completed by X number of days.
Then possibly run a batch process that scrubs and cleans up orphaned data on a daily basis.
Thoughts?
March 14, 2009 at 10:17 am
moojjoo (3/14/2009)
The key question that I have still is what is the industry standard. An idea I am also thinking about is does it really matter leaving orpaned data in the system? (Child tables not complete that should be?) as long as the application is smart enough to know where an end user left off filling out the data in the appliation and also do not provide any reporting on final reports of that data as well.Also, let the end users know that if the do not complete the entire entry process (were referring to a lot of data being captured in numerous web pages) that there data will be removed if not completed by X number of days.
Then possibly run a batch process that scrubs and cleans up orphaned data on a daily basis.
Thoughts?
Since there are several ways to arrive at the same ends, I wouldn't say there was an industry standard, although more & more people seem to be adopting using the OUTPUT clause. But one industry standard I can give you is that orphaned data is bad. No app is smart enough or well built enough to take into account the problems that can be caused by orphaned data. It can really muck up a system badly.
If you had to run a scrub... you could, but it would be second choice to maintaining the data properly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply