Multiple inserts
-
If you are using ASP.net keep everything in ViewState. If the application user has Insert rights on the tables just use Data Adapters's Isnsert/Update command. It will take care of your Identity Foreign key issues.
If there is a master table and child table (Most likely. It doesn't look like one one tables for me) it would be very difficult to make single stored procedure. It will be a Stored proecedure for each table.
I would use DataSet and Data Adapter of ADO.NET.
If you are using asp I would suggest to insert the data in each page.
Regards,
gova -
Thank you for all your help guys,
When creating the stored procedure, won't I need to do something 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),
AS INSERT INTO(SchoolName, SchoolCounty, SchoolType...) VALUES(@SchoolName, @SchoolCounty, @SchoolType...)
Or do I do the big insert outside of the stored procedure?
Thanks!
M
-
Everything inside.
BTW It's insert into dbo.TableName (...)
-
Remi,
Oh yeah, thanks.
BTW...why would I do all the inserts in the stored procedure?
My issue with this is, how the heck would I pass all the variables from the form into this stored procedure? That's like 250 variables!!!!
Thanks!
M
-
You can pass 1024 parameters
.Because you can put it all in 1 transaction and have it suceed all or fail all.
-
I thought it was 2100...!!!
at any rate...I know I'm happy when I don't have to pass more than about 10 (max in my procedures...thank god!)
**ASCII stupid question, get a stupid ANSI !!!** -
I don't think I can do this in ASP, because I'd have to pass the variables like so:
'all the variables from all the forms
SchoolName = SchoolName
SchoolCounty = SchoolCounty
SchoolType=SchoolType
etc....
set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.Open "DSN=webData;uid=user;pwd=password" 'make connection
strSql = "sp_myInsert '" & SchoolName & "', '" & SchoolCounty & "', '" SchoolType & "'" etc......
dataConn.Execute(strSql) 'execute sql call
The problem is, I can't do this for multiple tables...I mean, I'll be passing like 250 variables, but how will the stored procedure know what table they go into?
Thanks,
M
-
Your stored procedure will know because you tell it in your t-sql - eg: parameter1 goes to tbl1, parameter2 into tbl2 etc...
**ASCII stupid question, get a stupid ANSI !!!** -
hey remi - this's what I have from the microsoft website...
max parameters
"@parameter
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined or the value is set to equal another parameter). A stored procedure can have a maximum of 2,100 parameters."
hmm - I wonder if today's the day I'm finally right when remi's wrong?????
**ASCII stupid question, get a stupid ANSI !!!** -
You're right... must have been confused with server 7.0.
Anyways he can pass 250 without any problems... and I'm right on that one.
-
Forgive my ignorance, but how do I tell the stored procedure what variable goes where? Right now, I just my variables defined like:
@SchoolName varchar(50),
@SchoolCounty varchar(50),
@SchoolType char(1),
@NetworkType varchar(50)
etc...
How do I tell it that @SchoolName would go into the School table, and the @NetworkType variable would go into the Network table?
Thanks!
M
-
Insert into dbo.Schools (col1, col2) values (@Var1, @Var2)
-
magyar - it would be something like...
insert into tblSchool(SchoolName, SchoolCounty, SchoolType)
values(@SchoolName,
@SchoolCounty,
@SchoolType)
insert into tblNetwork(NetworkType)
values(@NetworkType)
...etc...
**ASCII stupid question, get a stupid ANSI !!!** -
Hey guys, it's me again...I've been doing some research on stored procedures, and thinking about all the advice I receieved.
I started to write out all the variables that the stored procedure would require, and after I was done with the list, I couldn't believe it!
After looking it over, do you think a stored procedure is still the best way to go?
create procedure sp_biginsert (
@SRrequestDate varchar(50)
@SRschoolNameFirst varchar(150)
@SRschoolNameLast varchar(150)
@SRschoolCompany varchar(150)
@SRschoolBranch varchar(150)
@SRschoolAddress1 varchar(150)
@SRschoolAddress2 varchar(150)
@SRschoolCity varchar(150)
@SRschoolState char(10)
@SRschoolZip varchar(20)
@SRschoolPhone varchar(35)
@SRschoolFax varchar(35)
@SRschoolEmail varchar(50)
@SRschoolRegion varchar(75)
@SRschoolSupervisor varchar(75)
@SRschoolclaimType varchar(50)
@SRschoolDateOfLoss varchar(50)
@SRschoolInsured varchar(50)
@SRschoolClaimNumber varchar(50)
@SRschoolPackaging varchar(50)
@SRschoolContactPrefs varchar(50)
@SRclaimantNameFirst varchar(150)
@SRclaimantNameLast varchar(150)
@SRclaimantOffice varchar(150)
@SRclaimantAddress1 varchar(150)
@SRclaimantAddress2 varchar(150)
@SRclaimantCity varchar(150)
@SRclaimantState char(10)
@SRclaimantZip varchar(20)
@SRclaimantPhone varchar(35)
@SRclaimantDateOfBirth varchar(25)
@SRclaimantSSN
@SRclaimantSex
@SRclaimantMaritalStatus
@SRclaimantSpouse
@SRclaimantChildrenNum
@SRclaimantChildrenAges
@SRclaimantRace
@SRclaimantHeightLow
@SRclaimantHeightHigh
@SRclaimantWeightLow
@SRclaimantWeightHigh
@SRclaimantSpecialChar
@SRclaimantOccupation
@SRclaimantInjury
@SRclaimantRestrictions
@SRclaimantRepresented
@SRclaimantVehicle1Make
@SRclaimantVehicle1Model
@SRclaimantVehicle1Tag
@SRclaimantVehicle2Make
@SRclaimantVehicle2Model
@SRclaimantVehicle2Tag
@SRCollege
@SRCollegeNameFirst
@SRCollegeNameLast
@SRCollegeAddress1
@SRCollegeAddress2
@SRCollegeCity
@SRCollegeState
@SRCollegeZip
@SRCollegePhone
@SRCollegeContact
@SRCollegeInfo
@SRattorneyNameFirst
@SRattorneyNameLast
@SRattorneyAddress1
@SRattorneyAddress2
@SRattorneyCity
@SRattorneyState
@SRattorneyZip
@SRattorneyPhone
@SRattorneyCopyOnReports
@SRattorneyContact
@SRattorneyInfo
@SRcenterCompany
@SRcenterNameFirst
@SRcenterNameLast
@SRcenterAddress1
@SRcenterAddress2
@SRcenterCity
@SRcenterState
@SRcenterZip
@SRcenterPhone
@SRcenterContact
@SRcenterInfo
@SRphysicianNameFirst
@SRphysicianNameLast
@SRphysicianAddress1
@SRphysicianAddress2
@SRphysicianCity
@SRphysicianState
@SRphysicianZip
@SRphysicianPhone
@SRphysicianContact
@SRphysicianInfo
@SRreason
@SRobjective1
@SRobjective2
@SRobjective3
@SRbudgetAmount
@SRsingleDaySurv
@SRsceneInvestigation
@SRactivityCheck
@SRresidencyCheck
@SRmultidaySurv
@SRstatement
@SRbackgrounCheck
@SRwidowCheck
@SRhospitalCanvass
@SRdueDate
@SRcomments
-
112 parameters you are still very far from 2100
Now compare that with all you would need to do client and server side if you go differently !
1. one call per insert
2. Intermediate storage before everything is good to go
3. Transaction handling for each trip
4. Serveral extra round trips to the DB
... etc
vs.
a single call at the end when you know everything needed with transaction handling included
* Noel
Viewing 15 posts - 31 through 45 (of 167 total)
You must be logged in to reply to this topic. Login to reply