Multiple inserts
-
Hi Folks,
I need some opinions on something.
I have a web form that spans across 5-pages. Each form page corresponds to a table in the SQL Server 2000 database. So I have 5 tables in all.
My issue is, what's the best way to write(INSERT) everything into the tables and keep all the data tied together?
I've thought about doing an INSERT command after each page is finished(when user hits NEXT), and I've also thought about just doing one giant INSERT command at the very end when the user is all finished.
The last page is going to contain the entire form so the user can review the data he/she entered and make changes as needed.
Also, each table needs some type of key to relate all the data together. For example, if the user fills out page 1 of the form, then goes to page 2 and fills that form out, I need a number for page 1, page 2, etc that ties all the data together in the tables. I could use a primary key for this, but how do I INSERT this key from table to table?
Thanks,
M
-
M,
I will tackle the easy one first - considering the last page is a "summary" of all the fields the user can/wants to add, I would do the insert from here. Why insert the data and then update it later if the user has changed their mind?
As for the primary key - we would need a little more info here to be able to help you. Table structures (DDL if possible) - sample data - and any rules we should be aware of concerning the data and business.
Good Luck,
Darrell
-
DSP, I agree with you on the first step; I will do the INSERT on the last page.
As far as table structure goes, I have 5 tables: School, Students, Professors, Buildings, Computers. Each table will have a column titled UniqueId that unifies the data across the 5 tables.
All the tables look basically like this:
UniqueId int
SchoolName char
SchoolCount char
SchoolType chari
SchoolAdd1 char
SchoolAdd2 char
SchoolPhone char
SchoolFax char
SchoolIP char
SchoolInfo varchar
SchoolChart varchar
All of them will have the UniqueId column that ties the data together. Because each row in the 1st table has a corresponding row in the all the other tables.
Thanks!
-
Just create an stored procedure and pass all varibles to it.
Create all insert statements from the parameters wrapped in a transaction and check for errors after each one of them! at the end commit if successful rollback if otherwise
* Noel -
So beautifully simple and so simply beautiful....(sounds of applause - darrell I'm sure you join me on this one)!!!
**ASCII stupid question, get a stupid ANSI !!!** -
It sounds beautiful...but also extremely difficult. I wouldn't even know where to start!
-
magyar (what a lovely name - makes me think of gypsies and dancing)...all you have to know is how to call a stored procedure from your application - we'll help you with the rest...
**ASCII stupid question, get a stupid ANSI !!!** -
You know how to make an insert??
It takes one insert per table you want to insert data to.
Then after each insert make sure there was no error (search for err handling on this site). Wrap all this up in a transaction and you're done
. -
Thank yous
Ok , I can do an insert, and I can put together an error check, but how would I handle the UniqueId that servers as the key to link all the data across the tables?
For example, let's say I hit the last page, and hit FINISH. Is this when I execute the stored procedure? And the stored procedure then does all the inserts for me, and that's it?
It sounds simple, yet hard because I have no idea what I'm talking about.
-
You've nailed it - in concept atleast...now all you have to do is implement it!
**ASCII stupid question, get a stupid ANSI !!!** -
Even if I do somehow manage to write the stored procedure, how do I handle to uniqueid? How do I link all the tables together?
-
Just take it one step at a time, don't be afraid, you can do it
Lets assume that you are at the last page and you have managed through cookies or hidden fileds to get all the data at the last page!
after the last page is submitted you have all you need to call a proc like:
create procedure prc_modulename_insertbig (
@SchoolName varchar(50),
@SchoolCounty varchar(50),
@SchoolType char(1),
@SchoolAdd1 varchar(100),
@SchoolAdd2 varchar(100),
@SchoolPhone char(11),
@SchoolFax char(11),
@SchoolIP char(16),
@SchoolInfo varchar(250),
@SchoolChart varchar(256),
....
Declare @UniqueID int, @Err int
BEGIN TRAN
insert into Table1 ( Col2, Col3) values (@SchoolName,@SchoolCounty)
Select @UniqueId = Scope_Identity(), @Err =@@Error
if @Err <> 0 Goto QuitWithRollback
-- Now use the ID from above to the rest of the tables
insert into Table2 (ColID,Col2,...) Values (@UniqueID, @SchoolAddr,...)
repeat error handling till done!
at the end you will get
if @Err <> 0 Goto QuitWithRollback
COMMIT TRAN
Return 0
QuitWithRollback:
ROLLBACK TRAN
Return @Err
Keep in mind this is just an example!!
* Noel -
Insert into....
err check goes here
--that gives you the id of the last insert in the proc, then you can use that variable in another insert.
SET @IdVar = Scope_Identity()
-
you post on this site:
1) The five inserts you have to do!
2) any other common fields besides uniqueID ?! that'll certainly help reduce the # of parameters being passed.
3) if there are no other common fields, then you just have to pass all 10-15 fields from each table as variables
4) not sure what you mean by linking the tables - they're already being linked by uniqueid right ?!
5) is your uniqueid an identity column that increments automatically or is it something you insert ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Congrats on the big K Sushila.
Viewing 15 posts - 1 through 15 (of 167 total)
You must be logged in to reply to this topic. Login to reply