Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Stored Procedure for faster Data Entry

    Hi all I have a question can you have a Stored Procedure insert data into tables for you, for faster data entry? See I was thinking of creating a form with the necessary fields that need to be populated. And what the user would do is look up the record they want to populate then hit a comand button that has a SP behind it and have it insert the data into those fields. is there a way I can do that, because if a user has to insert the same data over and over again but for different records then this would make it faster for them. Can anyone tell me how I can do that.

  2. #2
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I bet this is a silly question

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Yes you can.

    the question is why would you want them

    if you have a properly normalised database then there should be no need to reenter data that is already in there, you would just need to look up the ID and use that to relate

    so as an example a customer has 5 invoices
    you don't enter the custers details 5 times you have a customer table ands
    as that invoice 1 2 3 4 and 5 related to customer 1
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Here is the database Strucuture. I have one parent table its called the People_tbl, and the child tables are the activities of the people. Now the user looks up the person then enters their activities (in the child table) Now MS has a shortcut key (Insert the data from the same field in the previous record CTRL+' ) what i does is when the user enters in activites from the history table which would be

    [Earned Hours], [TypeOfContact], [Purpose of Contact], [Catagory for hours] and [Services Covered]

    all of these fields are in the child table (TanfActivity_tbl) not the parent. Does that make sense??

    so its the People_tbl one to many with TanfActivity_tbl

    Well different People could be goign to the same acitivity for the same amoutn of hours, thats where the Ditto key comes in. CTRL+ Apostrophe

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    what you probably should have is people --> PeopleActivity <-- Activity

    so your PeopleActivity contains
    people 1, activity 2
    people 2, activity 2

    but if what you want is to duplicate rows you just need an insert query that uses a select query as it's data source
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Not exactly sure how to do that?? would I create an insert statement

    Code:
    SELECT     EventDate, [Earned hours], [Catagory for hours], [Services Covered], [State Catagory], [State Services Covered], [Parent ID]
    FROM         TanfActivity_tbls

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    like this

    Code:
    INSERT INTO DestinationTable
                          (Col1, Col2, Col3, Col4,...)
    SELECT     ct.Col1,ct.Col2,ct.Col3,ct.Col4,...
    FROM         CopyTable AS ct
    WHERE ct.PrimaryKey = @CopyID
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    to use your select statement
    Code:
    INSERT INTO TanfActivity_tbls
              (EventDate, [Earned hours], [Catagory for hours], [Services Covered], [State Catagory], [State Services Covered], [Parent ID])
    SELECT     EventDate, [Earned hours], [Catagory for hours], [Services Covered], [State Catagory], [State Services Covered], [Parent ID]
    FROM         TanfActivity_tbls AS ct
    WHERE ct.PrimaryKey = @CopyID
    you didn't say what your key was so you will have to set that
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    also i'd suggest you have a quick read through Database Normalization Basics for future reference. it's quite well written and gives you the basics of normalisation which will help you improve your future DBs
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    The Database has One people_tbl with 4 Child tables so its a one- to many relationship the people table has all the participants personal info in it like names, Address, phone..etc. and the child tables have all their activities with the program. So the tanfactivity_tbls is their activities with the program. The reason why I wanted the Sp is becasuse when 10 or 12 participant go to an event for the same event, I wanted the users to have a faster way of entering in the participants hours. Thats why I wanted a Sp to help them ente that information faster

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We've covered this one before haven't we?

    The stored procedure should handle the mechanism for inserting the data (i.e. it accepts a load of parameters and sticks them in to the table).
    The User Interface handles what is inserted into the table (i.e. by passing a load of parameters to the stored procedure).

    So - if you want to insert a load of common data into these rows then it is the job of the User Interface to handle this.

    Also, to cover your precise example it sounds like this is not correctly normalised. The hours of the activity are (most likely) attributes of the activity, and not attributes of the person-activity relationship.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pootle, I have a bit of insight into Desiree's problem.

    Think of the People as Clients, the Activities as Inventory, and the People-Activity as invoice line items. At least this part of the schema is in 3NF.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think everyone is getting a little off track from what the OP wants. I think what is trying to be asked is for the inputs from last transaction not be erased.
    If that's the case then it should have nothing to do with a stored proc, just keep the data page.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dav1mo
    I think everyone is getting a little off track from what the OP wants. I think what is trying to be asked is for the inputs from last transaction not be erased.
    If that's the case then it should have nothing to do with a stored proc, just keep the data page.
    That (I think) is my point too - the UI should be handling this.

    Pat - I wasn't sure the schema was not normalised which is why I qualified that part of reply a fair bit. If you reckon this is ok then ignore that part Desi. I still think the bulk of the work, however, is for the UI to handle.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    I still think the bulk of the work, however, is for the UI to handle.
    Agreed, wholeheartedly.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •