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.

  • sushila 

    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